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

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

      • RobJo's avatar
        RobJo
        Helper I
        9 years ago

        i solved a simular issue on this by creating a calculated column in Power Query. where i integrateed the delta based on a index with a function (delta over a group of records, like by week). Anyone interested in this solution? send me a email and i will reply the example pbix. [email protected]

    • dkay84_PowerBI's avatar
      dkay84_PowerBI
      Microsoft Employee
      9 years ago

      I looked at you PBIX file and figured out where our misunderstanding is.  In turn this led me to change the "Return" line so that if used in a card, it wouldn't give an error.  The error was because the measure was returning multiple values when no filter is applied, so I added the HASONEFILTER condtion:

       

      RETURN
      if(HASONEFILTER(Table1[Week])=TRUE(), if(delta <>BLANK(),delta-1,BLANK()),"N/A")

       

      Now, if a user hasn't filtered by week number, they see "N/A" (I might change that to "Please select a week to see YoY % change"), otherwise they see the delta.

       

      Thanks for your help!