Forum Discussion
Post PartisanDAX for Financial Year column
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
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
),
2 Replies
- SamWiseOwl
Super User
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
),
RichOBThis was very helpful, thank you!