DAX for Financial Year column

[RichOB]

Hi, I'm looking to get the DAX just for the financial year, please.

I have individual columns for each of the below, what would be the DAX to get an individual column for the financial year? 

Year = Format ('Calendar' [DATE], "YYYY")

Month = FORMAT ('Calendar' [DATE], "MMMM")

Quarter = "Q" & Quarter ('Calendar' [DATE])

Financial Quarter = "Q" & Quarter (EDATE('Calendar' [DATE], -3))

Thanks

[SamWiseOwl · Accepted solution]

Hi @RichOB

Here is a blog on generating a basic calendar table: https://www.wiseowl.co.uk/blog/s2947/calendarauto-table.htm

The code you want is:

"Financial Year" = IF(

[Date] >= DATE(Year([Date]), 4, 1),

Year([Date]) & "/" & RIGHT(Year([Date]) +1,2),

Year([Date])-1 &"/" & RIGHT(Year([Date]),2)

)

"Financial Quarter" =SWITCH(

TRUE(),

MONTH([Date]) IN {4,5,6},"Qtr 1",

MONTH([Date]) IN {7,8,9},"Qtr 2",

MONTH([Date]) IN {10,11,12},"Qtr 3",

"Qtr 4"

)

// financial month within quarter

"Financial Month"  = IF(

MONTH([Date]) >= 4,

MONTH([Date]) - 3,

MONTH([Date]) + 9

),

[RichOB]

This was very helpful, thank you!