Search code examples
powerbipowerpivotdax

DAX SUM previous period integer value

This issue seemed to be easy at the first glance but I have been trying to solve it for a while.

enter image description here

I would like to dynamically sum the previous period sales in a power pivot measure. The thing is that my period column is a integer value not a date.

I manage to calculate the previous period but I did not manage to set it up as a filter value:

Max(Table1[Period])-1 --> this gives me the previous value of the period field

However when I want to add this as a filter of a calculated measure it doesn't work: --> Calculate( Sum(table1[Sales]), Filter(table1,Max(table1[Period])=Max(table1[Period]) )

I tried simply this one as well: Calculate(Sum(table1[Sales]), table1[Period] = table1[Period] -1 )

but neither of them are working. I though that I do it with calculated column however I would rather do it with measure.

Can you please help me?

Expected result:

Solution

  • Create measure:

    Previous Sales:= 
   CALCULATE( SUM(Table1[Sales]), 
              FILTER( ALL(Table1), Table1[Period] = MAX(Table1[Period]) - 1))

    It will give you dynamic previous sales. Please note: it relies on the fact that periods increment by 1.

    If you need to summarize Previous Sales, create a second measure:

    Total Previous Sales:=
   SUMX( VALUES(Table1[Period]), [Previous Sales])