The data comes in a Running Total. Meaning, you can’t sum the data to get your Quarter, you need to use the last month in the Quarter to represent the Quarter. Similar for YoY, it’s the last month in the year that represents the total sum for the Year.
I’m very much struggling to create the basic time intelligence views because of the running totals and custom FY I can’t get the built in functions like parelleperiod to work.
in the PBX there is a basic FY21 vs FY22 that i need to recreate for Month and Q using the proper Fiscals.
Any help to get on the right path would be great!.
The problem here is that all of the canned time intelligence functions are trash (in this case PARALLELPERIOD), and don’t work with weeks, fiscal years, non-standard calendars, etc. The good news is there’s a better way that will solve all your problems…
I watched the video and I use the Offsets most times. What I can’t get past is the how the data is set up as a running total. So no matter what measure I create I don’t know who to change the measure into using the last month of the Quarter or Year as the number to present. The measures allways want to sum up the values.
Oh, wait - I think I see what you’re getting at here in terms of the initial data being running total. I ran into the exact same problem with the Johns Hopkins COVID Data in Data Challenge 10.
Use the “Feldmann Shift” to obtain the previous period value in Power Query. The take the difference between those to to calculate the delta for each period. This will express the data in units per period, not cumulative units per period. Makes everything MUCH easier to work with, and you can always aggregate UP to cumulative totals using the approach above.
