Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 n...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Kaviraj11's avatar
Kaviraj11
Solution Sage
1 year ago

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()
)
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 !