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

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

11 Replies

  • 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 () )