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

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 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!