Fiscal Calendar Month Calculation

[raassd]

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 !

[v-kaiyue-msft · Accepted solution]

Hi @raassd ,

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.

[raassd]

@v-kaiyue-msft ,

Awsom !!! 

This is working as expected, Hope I can change my end date calendar as per the requirmnet and hope that will not change the calculations ?

Thank you soo much !!!

[Kaviraj11]

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?

[raassd]

@Kaviraj11 ,

How this will work for next year ?

Thnaks !

[Kaviraj11]

If you have dates available in your calendar table then you will have an option for next year

[Kaviraj11]

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()
)

[raassd]

@Kaviraj11 ,

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 !