Switch true dax query to get fiscal year

[Anonymous]

Hello 

I am desiring to create a calculated table that based on the date should return FY2020, FY2021, FY2022, FY2023

SWITCH(TRUE(),'Market Year Dates'[Date] > 4-1-2020 && 'Market Year Dates'[Date]< 3-30-2021, 2021, 'Market Year Dates'[Date] > 4-1-2021 && 'Market Year Dates'[Date] < 3-30-22,2022, 'Market Year Dates'[Date] > 4-1-2022 && 'Market Year Dates'[Date] < 3-30-23,2023)

This above query is not working. 

So I tried if statements 

IF(

'Market year dates'[date] < Date(2020,10,1) && 'Market Year Dates'[Date] < date(2021,09,30),

2021,

IF( 'Market year dates'[date] < Date(2021,10,1) && 'Market Year Dates'[Date] < date(2022,09,30),

2022,

IF('Market year dates'[date] < Date(2022,10,1) && 'Market Year Dates'[Date] < date(2023,09,30),

2023

)

))

[ppm1]

You can more easily create a FY column with an expression like this (just use the right month offset in EOMONTH; not sure if you need 3 or 9).

FY = "FY" & YEAR(EOMONTH('Market year dates'[Date], 3))

Pat

[Anonymous]

Hey I tried this measure but the visual slicers are not working. When I choose 2020, 2022 etc data the visuals go blank but when I hard code '2022' the data is pulling up. I would like to see current year data and prev data.

[grandtotal]

Add a calculated column to the date table.

Column = "FY" & 'Table'[Year]

[Anonymous]

I am trying to allow anyone to use a slicer to switch between my23 data and my21 data a dax query that will help my data understand to pull MY23 data My22 or My21 when required

[v-jingzhang]

Hi @Anonymous 

You can add a fiscal year column with below DAX in the Date table. 

Fiscal Year = "FY" & IF(MONTH([Date])<4,YEAR([Date]),YEAR([Date])+1)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!