I currently have a cost facts table with a MonthYear column that records the fiscal month year eg. Sep FY 24 (actual september 2023), which is also the startyearmonth.
My current solution is breaking the date into year, quarter, month columns text types,
example:
FiscalQuarter =
VAR Month = 'fy24_monthly'[Month]
RETURN
IF ((
Month = 9|| Month = 10|| Month=11 ),"Q1",
IF ( (
Month = 12|| Month = 1|| Month = 2 ),"Q2",
IF( (
Month= 3|| Month =4|| Month =5 ),"Q3","Q4")))
which allows me to use slicer just fine, but it comes to drilling down charts i need hierarchical date column
Date = DATE(
IF(VALUE(fy24_monthly[FiscalYear]) < 100, 2000 + VALUE(fy24_monthly[FiscalYear]), VALUE(fy24_monthly[FiscalYear])),
VALUE(fy24_monthly[Month]),
1
)
if i do this the quarter the grouped incorrectly, and the starting month wont be September.
What solutions are there to fix this?
First of all, the rule of thumb is to stick to a date-based calendar table;
secondly, “offset dates” pattern can easily tackle such a fiscal year scenario.
Fiscal Yr-Qtr = FORMAT( EDATE( DATES[Date], 4 ), "yyyy-QQ" )
1
pls check if this is what you want
quarter =
VAR _month =
MONTH ( 'Table'[date] )
RETURN
SWITCH (
TRUE (),
_month IN { 9, 10, 11 }, "Q1",
_month IN { 12, 1, 2 }, "Q2",
_month IN { 3, 4, 5 }, "Q3",
"Q4"
)
year =
IF (
MONTH ( 'Table'[date] ) < 9,
YEAR ( 'Table'[date] ),
YEAR ( 'Table'[date] ) + 1
)
1