Same calculation one year ago

DAX DAX Calculations
1

Hi all,

I am having issues trying to calculate a value from the previous fiscal year.
The data is a little odd in that last years data is provided at a quarterly level of granularity and this years is monthly.

So the initial calculation takes this into account, and seems to be working fine, but I am having issues with the calculation for the previous financial year. But feel I need an ALL on the filter to change the filter context and bring back the entire data calendar. If using FILTER( ALL( is correct, the syntax is killing me.

I have attached the pbix for download.

Any help would be greatly appreciated.
@BrianJ, it is similar to the one you solved for me, I have tried my best, :frowning:

John

Update: I had the FY start incorrect in the last file

edna v02.pbix (83.4 KB)

2

@jgriffit,

Dealing with mismatched granularities is always tricky, and this one is no exception – much more difficult than the one we worked on yesterday. Per my previous post in the last thread about time intelligence, we made good use here of the quarterly offset in the extended date table. Here’s what I did:

  1. In the slicer that contains fiscal year and fiscal quarter, I harvested the selected quarterly offset

  2. I created a disconnected physical helper table that added the relevant quarterly fields from the date tabl, using the RELATED function. (Note: it would be cleaner to do this as a virtual table variable within the measure in #3 below, but the DAX to do so is significantly more complicated and I thought this would be an easier way to show what was going on in the process).

    SummTable =

     VAR vTable =
 ADDCOLUMNS(
     Data,
     "QnY", RELATED( 'Calendar'[QuarternYear] ),
     "QOffset", RELATED( 'Calendar'[QuarterOffset] )

 )

 RETURN
 vTable

  3. Created the following measure to filter the summary table above, based on the harvested fiscal quarter minus 4 (i.e., stepping it back four quarters to the same quarter of the previous fiscal year):

    Val Prev FY FQ =

     CALCULATE(
     SUM( SummTable[Val] ),
     FILTER(
         'SummTable',
         'SummTable'[QOffset] = [Harvest Q Offset] - 4
     )
 )

Here’s what it looks like all put together::

image

I hope this is helpful. Full solution file posted below. I also will try to dig up some videos that walk through these concepts in detail.

UPDATE:
P. S. Here are some excellent videos that address the challenge of working at multiple granularities:

https://forum.enterprisedna.co/t/managing-data-at-different-granularities/3439

https://forum.enterprisedna.co/t/the-ultimate-budget-allocation-methodology-for-power-bi-analysis/4069

https://forum.enterprisedna.co/t/actuals-vs-budgets-comparison-insights-w-power-bi/4071

1 Like
3

Thanks again Brian. There were a few challenges in that one! Your assistance is very much appreciated.

Now enjoy the weekend!

1 Like
4

@jgriffit,

One thing I should have mentioned above is that the are two ways to deal with the granularity mismatch:

  1. You can aggregate the higher granularity (months, in this case) up to the level of the lower granularity (quarters, here); or

  2. You can allocate the lower granularity down to the level of the highest.

Which you use will depend on the nature of the problem and the desired outcome. #1 is easier, #2 is more common (most budgeting scenarios fall into the latter).

In the videos above, @sam.mckay goes through his revised methodology for #2 - it’s absolutely worth taking the time to understand that approach to the point that you can construct and apply it yourself, since it’s a common scenario and the tools he provides are generalizable to a lot of different situations.

  • Brian
5

Thanks Brian. I do understand and will continue to work at it / practice scenarios.
Thank you very much for your support over the last couple of weeks.
Kind regards
John

1 Like