Compare current year and prior year data based on fiscal year

[ABR002]

So I've found some similar questions on this but not one that matches mine. I need to compare data of the year someone selects with a slicer to the year most recent to that selection, so if someone selects 2020 I would want to see 2020 Values/2019 Values. One of the kickers here is that I'm going off of the fiscal calendar that my firm uses, which changes year by year. So I can't do a Same Period or Previous Year measure. I have a date table that assigns all the dates to the right fiscal year and month, no problem there. But I can't figure out how to do a comparison with the current (selected) fiscal year and the prior fiscal year. This isn't going to be used that far into the future, so I tried doing a measure that assigns the sum based on the fiscal year in the column:

var Figure21 = Calculate([Total Sales Sum],Data[Fiscal Year] = 2021)

var Figure22 = Calculate([Total Sales Sum],Data[Fiscal Year] = 2022)

var Figure23 = Calculate([Total Sales Sum],Data[Fiscal Year] = 2023) etc

var Selection = 

for this one I tried to do a Selectedvalue or Max of the fiscal year based on both the table I'm using as well as a separate (unlinked, relationshipless) table. I then do a return based on that Selection variable:

if(Selection = 2022,Figure22/Figure21,

if(Selection = 2023,Figure23/Figure22,

if(Selection = 2024,Figure24/Figure23, etc

and I either get infinity (with Max) or like a -93% (with Selectedvalue), I'm looking for -72%. 

Any ideas? If we could just go by a set fiscal year this would be a lot easier.

[ABR002]

@amitchandak Edit: Thanks, I tweaked it a bit. Needed to use AllExcept for the filters instead of all. 

[amitchandak · Accepted solution]

@ABR002 , If you have date table, Based on year end date

Example year-end at March 21

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))

With help from a separate Year or date table

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[laraibashfaq]

Hey on your below solution how can we split it in Qtr's?

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))