Forum Discussion

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago
Solved

Period over Period Change Measure

I am building a report that will calculate deltas for sales and quantity.  The sales and quantity values returned from the query to the DB are aggregated, so I have a table that returns Product Name,...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    dkay84_PowerBI

     

    having this sample table:

     

    year and week are calculated columns

     

    create a measure:

     

    Delta% =
VAR yearselected =
    VALUES ( Table1[Year] )
VAR weekselected =
    VALUES ( Table1[Week] )
VAR delta =
    DIVIDE (
        CALCULATE ( SUM ( Table1[Sales] ) ),
        CALCULATE (
            SUM ( Table1[Sales] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Year]
                    = yearselected - 1
                    && Table1[Week] = weekselected
            )
        )
    )
RETURN
    IF ( delta <> BLANK (), delta - 1, BLANK () )
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    I am not getting your solution to work with just the week number slicer.  However, since in this case there will always just be current and previous year, I changed the code to the following and it works:

     

    Delta% = 
VAR yearselected =
    MIN(Table[Year])
VAR weekselected =
    VALUES ( Table[Week#] )
VAR delta =
    DIVIDE (
        CALCULATE ( SUM (Table[Sales] ) ),
        CALCULATE (
            SUM ( Table[Sales]  ),
            FILTER (
                ALL ( Table ),
                Table[Year]
                    = yearselected
                    && Table[Week#] = weekselected
            )
        )
    ) - 1
RETURN
    IF ( delta <> BLANK (), delta - 1, BLANK () )

    The red code is where I adjusted

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

dkay84_PowerBI

 

having this sample table:

 

year and week are calculated columns

 

create a measure:

 

Delta% =
VAR yearselected =
    VALUES ( Table1[Year] )
VAR weekselected =
    VALUES ( Table1[Week] )
VAR delta =
    DIVIDE (
        CALCULATE ( SUM ( Table1[Sales] ) ),
        CALCULATE (
            SUM ( Table1[Sales] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Year]
                    = yearselected - 1
                    && Table1[Week] = weekselected
            )
        )
    )
RETURN
    IF ( delta <> BLANK (), delta - 1, BLANK () )
dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

Thanks Vvelarde this works!  However, I don't want the user to have to select a year, just a week.

 

In your example table, I would have a slicer with Week that would just have 1, 2, and 3.  If they select 2, then week 2 for 2015 and 2016 would be displayed, and I want the resulting delta.  

 

 

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    dkay84_PowerBI

     

    If i replace week instead of weekid

     

    • dkay84_PowerBI's avatar
      dkay84_PowerBI
      Microsoft Employee
      9 years ago

      I tried replacing all instances of week with weekID in your code and it is giving me an error.

       

      Would you mind posting the actual Delta formula?

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        dkay84_PowerBI

         

        I don't touch the original DAX.

         

        Only replace in slicer the weekid by week.

         

        In a table visual disabled the totals because send you a error. (i think that you dont need the totals so i jump to manage this part in dax). 

         

        PBIX Sample

    • dkay84_PowerBI's avatar
      dkay84_PowerBI
      Microsoft Employee
      9 years ago

      I am not getting your solution to work with just the week number slicer.  However, since in this case there will always just be current and previous year, I changed the code to the following and it works:

       

      Delta% = 
VAR yearselected =
    MIN(Table[Year])
VAR weekselected =
    VALUES ( Table[Week#] )
VAR delta =
    DIVIDE (
        CALCULATE ( SUM (Table[Sales] ) ),
        CALCULATE (
            SUM ( Table[Sales]  ),
            FILTER (
                ALL ( Table ),
                Table[Year]
                    = yearselected
                    && Table[Week#] = weekselected
            )
        )
    ) - 1
RETURN
    IF ( delta <> BLANK (), delta - 1, BLANK () )

      The red code is where I adjusted

      • dkay84_PowerBI's avatar
        dkay84_PowerBI
        Microsoft Employee
        9 years ago

        I am still curious how you were able to implement your solution with just Week slicer (not year) but at least I have my measure