Forum Discussion
Fiscal Calendar Month Calculation
Team,
I am creating a Fiscal Cal Table where in Start date is from 04/02/2024 to 01/02/2024 for CY and next year it starsts from 02/02/2025 and goes on. attaching the screen shot for the same.
I need to create a year coulmn and Month Column if I select 2024 it should show from Feb 4 to Jan 1st 2025 and for Month If I select Feb it should show dates from 4th Jan to 2nd Feb, If I select Jun it should show all the values from 2nd Jun to 6th July as shown in the below image.
Please help me thanks in Advance !
Hi Anonymous ,
You can create a calculated table.
Table = VAR _vtable1 = ADDCOLUMNS ( CALENDAR ( "2024-1-1", "2025-2-28" ), "_Rank", RANKX ( FILTER ( CALENDAR ( "2024-1-1", "2025-2-1" ), MOD ( DATEDIFF ( "2024-2-3", [Date], DAY ), 7 ) = 0 ), [Date], , ASC ) ) RETURN SELECTCOLUMNS ( ADDCOLUMNS ( _vtable1, "month", FORMAT ( MINX ( FILTER ( _vtable1, [_Rank] = EARLIER ( [_Rank] ) ), [Date] ), "mmmm" ) ), [Date], [month] )Then create a calculated column.
Year = IF([month] = "January",YEAR([Date]) - 1, YEAR([Date]))If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi
Try this out
Year Column
To create a Year column that reflects your custom fiscal year, you can use the following DAX formula:
FiscalYear = IF( MONTH([Date]) >= 2, YEAR([Date]), YEAR([Date]) - 1 )Month Column
For the Month column, you need to account for the custom start and end dates within each month. Here’s a DAX formula to achieve this:
FiscalMonth = SWITCH( TRUE(), [Date] >= DATE(YEAR([Date]), 2, 4) && [Date] <= DATE(YEAR([Date]), 3, 1), "Feb", [Date] >= DATE(YEAR([Date]), 3, 2) && [Date] <= DATE(YEAR([Date]), 4, 1), "Mar", [Date] >= DATE(YEAR([Date]), 4, 2) && [Date] <= DATE(YEAR([Date]), 5, 1), "Apr", [Date] >= DATE(YEAR([Date]), 5, 2) && [Date] <= DATE(YEAR([Date]), 6, 1), "May", [Date] >= DATE(YEAR([Date]), 6, 2) && [Date] <= DATE(YEAR([Date]), 7, 1), "Jun", [Date] >= DATE(YEAR([Date]), 7, 2) && [Date] <= DATE(YEAR([Date]), 8, 1), "Jul", [Date] >= DATE(YEAR([Date]), 8, 2) && [Date] <= DATE(YEAR([Date]), 9, 1), "Aug", [Date] >= DATE(YEAR([Date]), 9, 2) && [Date] <= DATE(YEAR([Date]), 10, 1), "Sep", [Date] >= DATE(YEAR([Date]), 10, 2) && [Date] <= DATE(YEAR([Date]), 11, 1), "Oct", [Date] >= DATE(YEAR([Date]), 11, 2) && [Date] <= DATE(YEAR([Date]), 12, 1), "Nov", [Date] >= DATE(YEAR([Date]), 12, 2) && [Date] <= DATE(YEAR([Date]) + 1, 1, 1), "Dec", [Date] >= DATE(YEAR([Date]) + 1, 1, 2) && [Date] <= DATE(YEAR([Date]) + 1, 2, 3), "Jan", BLANK() )Thanks for your response !
I am not getting the required output for year and Month is also not working as expected
It is showing only Feb month more over I should get as 2024 not 2023.
Thanks !
Hi,
With above code, i was able to produce the output as you would like. Please check you table or relationships. Could you share a sample file?
Hi Anonymous ,
You can create a calculated table.
Table = VAR _vtable1 = ADDCOLUMNS ( CALENDAR ( "2024-1-1", "2025-2-28" ), "_Rank", RANKX ( FILTER ( CALENDAR ( "2024-1-1", "2025-2-1" ), MOD ( DATEDIFF ( "2024-2-3", [Date], DAY ), 7 ) = 0 ), [Date], , ASC ) ) RETURN SELECTCOLUMNS ( ADDCOLUMNS ( _vtable1, "month", FORMAT ( MINX ( FILTER ( _vtable1, [_Rank] = EARLIER ( [_Rank] ) ), [Date] ), "mmmm" ) ), [Date], [month] )Then create a calculated column.
Year = IF([month] = "January",YEAR([Date]) - 1, YEAR([Date]))If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
