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

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

Let me add that I don't necessarily need to view this as a table.  I can have a measure, and essentially after a user selects a slicer value for week, if there is a matching week from the earlier year (which there always should be, so no logic needed here), it will return the delta, otherwise "N/A" or something to the effect of "No earlier week exists".

 

So they select week 201633 from the slicer, and my measure will reflect the delta from week 201533.

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago

    So I have calculated columns which extract the year and week number from the WeekID column.  The result looks like this in a table visual (after a slicer for week 41 has been selected):

     

    I want my measure to calculate the resulting delta of week 41 YoY:

     

    (201641 Sales)/(201541 Sales) -1 = 10,993,021/13,442,475 - 1 = -18.2%

     

    • 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.