Comparing Fiscal Year Quarters (with option to drill-down to the month)

[Anonymous]

Goal: Compare Fiscal Quarters against each other for 2 fiscal years

Note: our fiscal year begins June 1st

DAX used:

Fiscal Year = IF('Date'[Month Num] <= 5, 'Date'[Year], 'Date'[Year] + 1)

Fiscal Quarter = IF('Date'[Month Num] >= 6 && 'Date'[Month Num] < 9 , "FQ1",

IF('Date'[Month Num] >=9 && 'Date'[Month Num] < 12, "FQ2",

IF('Date'[Month Num] = 12 || 'Date'[Month Num] = 1 || 'Date'[Month Num] = 2, "FQ3", "FQ4")))

Data Structure: Simple 2-table (data table and date table)

 

I created a hierarchy by dragging fiscal quarter and then month to fiscal year and the calculations are working:

From here, I just need to be able to overlay the FY18 & FY19 so the periods can be compared. This would ideally be able to be viewed as FY18FQ1 against FY19FQ1 as well as being able to drill down to see FY18FQ1'June' vs FY19FQ1'June' etc. 

Any help is greatly appreciated!

[Anonymous · Accepted solution]

@Anonymous -

It looks like your Date table has a column that is type Date. That is good. Now, make sure you have that table "Marked as Date Table". Then you can use the power "Time Intelligence" functions in DAX.

For example, if you have a Measure called Value, you can create another Measure called Value Last Year:

Value Last Year =
CALCULATE ( [Value], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Then, put both measures in your line chart.

Cheers!

Nathan

[Anonymous]

Thank you, that helped a lot. Is there any way to trim the results so as to exclude the FY18 values prior to the comparison period as well as excluding the "Last Year" values that have not yet had a comparison period for this FY? Ideally, I would like it to show only the active comparison period. Thanks again!

[Anonymous]

@Anonymous - You'll need to add a filter/slicer to limit to only the time-frame you're interested in comparing.

[Anonymous]

Got it working now. Thank you so much!