topic Re: achieving financial year FYYYYY/YY in DAX in DAX Commands and Tips

achieving financial year FYYYYY/YY in DAX

AG_x11x — Fri, 27 Oct 2023 05:03:04 GMT

Hi friends,

I need the DAX formulas for my Custom Financial year.

I would like to add a column with my financial year showing:

my financial year is 1st July 2022 - 30th June 2023 and dax formula should have to display like: FY2022/23

any suggestions on how to achieve this would be appreciated.

Current code:

Central calendar = ADDCOLUMNS(

CALENDAR(dATE(2002, 1, 1), DATE(2030, 1,1)

, "YEAR", YEAR[DATE])

, "MONTHNUMBER", MONTH([DATE])

, "MONTH", FORMAT([DATE])

, "MONTH YEAR", FORMAT([DATE], "MMM YY")

, "FISCAL YEAR", YEAR(EDATE([DATE], 6))

, "FISCAL MONTH", MOBTH(EDATE([DATE], 6)

, "FISCAL WEEK", WEEKNUM(EDATE([DATE], 6)))

Thanks

Re: achieving financial year FYYYYY/YY in DAX

Jihwan_Kim — Fri, 27 Oct 2023 02:42:32 GMT

Hi,

Please try something like below if it suits your requirement.

Central calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2002, 1, 1 ), DATE ( 2030, 1, 1 ) ), "YEAR", YEAR ( [DATE] ), "MONTHNUMBER", MONTH ( [DATE] ), "MONTH", FORMAT ( [DATE], "mmm" ), "MONTH YEAR", FORMAT ( [DATE], "MMM YY" ), "FISCAL YEAR", "FY" & IF ( MONTH ( [DATE] ) < 7, YEAR ( [DATE] ) - 1 & "/" & FORMAT ( [Date], "yy" ), YEAR ( [DATE] ) & "/" & FORMAT ( EOMONTH ( [Date], 6 ), "yy" ) ) )

Re: achieving financial year FYYYYY/YY in DAX

FreemanZ — Fri, 27 Oct 2023 02:47:34 GMT

hi AG_x11x ,

try like:

dates = ADDCOLUMNS( CALENDAR(DATE(2002, 1, 1), DATE(2030, 1,1)), "YEAR", YEAR([DATE]), "MONTHNUMBER", MONTH([DATE]), "MONTH", FORMAT([DATE], "MMM"), "MONTH YEAR", FORMAT([DATE], "MMM YY"), "FISCAL YEAR", IF( MONTH([date])<=6, "FY20"&RIGHT(YEAR([date])-1, 2) &"/" & RIGHT(YEAR([date]), 2 ), "FY20"&RIGHT(YEAR([date]), 2) &"/" & RIGHT(YEAR([date])+1, 2 ) ), "FISCAL MONTH", VAR _month = IF( MONTH([date])<=6, MONTH([date])+6,MONTH([date])-6 ) RETURN _month )

it worked like: