How to get year over year comparisons for selected month, week, quarter, or year with fiscal years

[MLuna]

I am new to PowerBI and have been learning through Youtube videos.

Our company has a fiscal calendar that starts on different days each year. For example, the Fiscal Year starts around February, but the actual first day varies: 2025 FY started on 2/3, 2024 FY started on 1/29, 2023 FY started on 1/30, etc.

I have set up a fiscal calendar table that has all these specified dates:

The YTD calculcation is correct for current fiscal week, fiscal month, fiscal quarter and year and whichever way I slice it the current  comes out accurate.

However, when I try to compare by fiscal week, month, ytd fiscal quarter or ytd fiscal year vs last year using sameperiodlastyear function shown here (with or without the VAR/RETURN): 

It gives me inaccurate sales comparisons.

For example, here is what it shows for Q1 of FY 2025 (sales - this is correct) vs FY 2024 (PY Sales FY):

But this is what it should show in blue vs what it shows in yellow:

I can only get the fiscal week correct by using this:

However, I do not want it to be unique calculations for each parameter (fiscal week, fiscal month, fiscal quarter, fiscal year). I want to be able to click on a slicer with year, quarter, month, and fiscal week like this:

And have it be dynamic and correcly compare this same period to the fiscal week, month, quarter or ytd from the prior year.

Is there a way to do this?

@lbendlin @Greg_Deckler @Deku 

[Deku]

Var previousFY = dates[FY year] - 1

Return 

Calculate(

Something,

Treatas( {previousFY}, dates[FY year])

)

[MLuna]

Thanks, does your "dates[FY Year]" = my FW_CONVERTER[FY Year]?

[Deku]

Looks like that is your date table, so yes

[MLuna]

OK, when I change it, it gives me this error message:

[Deku · Accepted solution]

Oops wrap the year in max()

[MLuna]

You are AWESOME! So that worked for week, month and quarter. However, when I click on year in my slicer it shows ALL sales for FY 2024 and not just the same period comparison. Can I add something to this calculation to accomodate?

For example, FY 2025 started 2/3 so when I click 2025 it shows me the correct sales for FY 2025 through 3/17 but the FY comparison shows me all sales from 1/29/24 through 2/2/25 of FY 2024. instead of comparing it from 1/29/24 through 3/17/24.

[MLuna]

@Deku - I used my same filter (FW_CONVERTER[PF Date2] = TRUE ()) and added this as a filter to the formula you gave me and now it works for week, month, quarter and year! Thank you so much!

[johnt75]

You could create a new column in the date table which stores the corresponding date last year, using whatever logic you need so that day 1 of the FY this year equates to day 1 of the last FY, etc.

You could then create a measure like

Sales Prior FY =
CALCULATE (
    [Sales],
    TREATAS ( VALUES ( 'Date'[Date last year] ), 'Date'[Date] )
)

[MLuna]

How do I create a new column in the date table which stores the corresponding date last year, using whatever logic you need so that day 1 of the FY this year equates to day 1 of the last FY, etc.?