% difference from -- as YOY change -- but without calendar

[geog272]

Hello,

I have a datset like this:

| sessions | fiscal week | fiscal year |

I would like to calculate YOY change in sessions. In a pivot table, I would choose "% difference from" and "previous" and "fiscal year"

In Power BI, I have read about how to do this using a calendar. However, my dataset is aggregated at the level of fiscal week. The data includes fiscal week (as a number like 21, for example) and fiscal year (like "FY21"). 

What is the best way to calculate YOY change? Is there a way to create this as a calculated measure? I tried using "% difference" but without luck. Do I need to find a way to join this data to a standard calendar?

Thanks in advance.

[collinq]

HI @geog272 ,

You should almost always have a data table that you use.  There are plenty of examples of date tables on the web but here is a simple on that you can download:

Why Create a Date Table in Power BI? : :: Welcome To EPM Strategy ::

Then, you can link your dates to that date table.  And, you can use the built in DAX formulas to create your YOY (whether you use the quick measures or make your own).  

If you make your own, for here: Data Analysis Expressions (DAX) Reference - DAX | Microsoft Docs and select the Time Intelligence and then use something like "Sameperiodlastyear".

[geog272]

Thanks for the reply. I could join to a calendar, however, then it gets complicated. I would need to take my original data which is aggregated by the week and convert it to data that is aggregated by day in order to be able to join to a calendar.

The reason the data is currently aggregated by week is that it just becomes too large to process if I don't aggregate it by week.  Maybe this just means that Power BI is not the ideal tool for my data. 

[collinq]

Hey @geog272 ,

You don't necessarily have to have this at day level.  A Date Table can have a week level capability and you can join week to week and not day to day.  The level that you join your data to the date table is up to you.  The Date Table can go all the way to minutes but you don't have to use them.  Or, you could create/modify your date table so that it doesn't even include days.  The data table can be whatever level you feel is needed.

[geog272]

Thanks for your reply. I'll see if I can make a concatenated field to join to a calendar on -- something like week + fiscal year.

[collinq]

Hey @geog272 ,

Please mark my response as a solution if that worked for you!  Let us know if you have any other issues!

[geog272]

I will mark as solution if I can get it to work... so far, no success... thanks

[v-shex-msft]

Hi @geog272,
Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng

[geog272]

Hi @v-shex-msft 

Thanks for following up. Here is a sample dataset: https://drive.google.com/file/d/1TkKIvuLBlhtBbbUKFhbAI3jR49XlGlw9/view?usp=sharing

And here is an example of a YOY comparison that I would need to do. I often need to do this as week over week as well.

Thank you for your help!

[v-shex-msft · Accepted solution]

Hi @geog272,

You can test to use following measure expressions if they suitable for your requirement:

YOY Click% = 
VAR currFY =
    MAX ( 'Table'[FiscalYear] )
VAR prevFY =
    "FY"
        & ( RIGHT ( currFY, 2 ) - 1 )
VAR cData =
    SUM ( 'Table'[clicks] )
VAR pData =
    CALCULATE (
        SUM ( 'Table'[clicks] ),
        FILTER ( ALLSELECTED ( 'Table' ), [FiscalYear] = prevFY ),
        VALUES ( 'Table'[device] )
    )
RETURN
    DIVIDE ( cData - pData, pData )

YOY impressions% = 
VAR currFY =
    MAX ( 'Table'[FiscalYear] )
VAR prevFY =
    "FY"
        & ( RIGHT ( currFY, 2 ) - 1 )
VAR cData =
    SUM ( 'Table'[impressions] )
VAR pData =
    CALCULATE (
        SUM ( 'Table'[impressions] ),
        FILTER ( ALLSELECTED ( 'Table' ), [FiscalYear] = prevFY ),
        VALUES ( 'Table'[device] )
    )
RETURN
    DIVIDE ( cData - pData, pData )

Regards,

Xiaoxin Sheng