Period over Period Change Measure

[dkay84_PowerBI]

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, WeekID, Quantity and Sales.  The WeekID is in the form: 201501, 201502 etc. for year 2015, week 1, week 2 etc.  The way this query was set up is that it will bring up sales/quantity for the same period for multiple years, so my table will have WeekID like the following:

WeekID

201501

201502

201503

201601

201602

201603

The year and week range will always be identical for offset years, but the years and the week range may differ (it could be 201532 and 201632, for example).

I understand the DAX to calculate delta, but is there a way to dynamically calculate the weekly change, year over year, without hard coding in filter arguments for week number?  

My end result is that I want a measure (calc column?) that will cacluate the YoY% change for each week.  Something like a table that would display the following:

WeekID1  WeekID2  Delta

201501     201601    X%

201502     201602    X%

...               ...             ...

Let me reiterate that the week/year depends on the underlying data which I have no control over.  So maybe some way to return a table that shows the matching values for week and then a calculated column for delta?

Appreciate any help

[Vvelarde · Accepted solution]

@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]

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]

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 · Accepted solution]

@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]

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]

@dkay84_PowerBI

If i replace week instead of weekid

[dkay84_PowerBI · Accepted solution]

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]

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!

[dkay84_PowerBI]

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]

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. rob@digidoen.nl

[dkay84_PowerBI]

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]

@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