Forum Discussion

Jerzee's avatar
Jerzee
Regular Visitor
1 year ago
Solved

Salutations-Fiscal Calendar in Power BI

I am struggling making a fiscal calendar. Mainly with generating the "Fiscal Month/Period" and how to get the month number. It was explained to me but I still cant grasp it. I wish coursera had actual live people to help us students, rather than a BOT. I am sorry if this is a burden. I am really trying. 

 

Thank you Microsoft team,

 

Regards,

Jerz

  • Hi Jerzee 

     

    Different companies might have different ways of them deciding a financial year .

     

    for the below example of calculated table you can create a fiscal calender staring from April 

     

    Date Table = 
    ADDCOLUMNS(
        CALENDAR(DATE(2015,4,1), DATE(2030,3,31)),
    
        -- Fiscal Year
        "Fiscal Year", 
            "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00"),
    
        -- Fiscal Year-Quarter
        "Fiscal Year-Quarter", 
            "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00") & " " &
            "Q" & 
            SWITCH(
                TRUE(),
                MONTH([Date]) IN {4,5,6}, 1,
                MONTH([Date]) IN {7,8,9}, 2,
                MONTH([Date]) IN {10,11,12}, 3,
                MONTH([Date]) IN {1,2,3}, 4
            ),
    
        -- Month Name (Text)
        "Month", FORMAT([Date], "MMMM"),
    
        -- Actual Month Number (Calendar Month 1 = Jan)
        "Actual Month Number", MONTH([Date]),
    
        -- Fiscal Month Number (April = 1, May = 2, ..., March = 12)
        "Fiscal Month Number", MOD(MONTH([Date]) + 8, 12) + 1
    )

     

  • Hi Jerzee 

     

    Try the sampe DAX calculated table below

    CalendarTable =
    VAR FinancialYearStartMonth = 7 -- set your financial year start month here (e.g., 7 = July)
    RETURN
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2030, 12, 31 ) ),
            "Year", YEAR ( [Date] ),
            "Month", MONTH ( [Date] ),
            "Month Name", FORMAT ( [Date], "MMMM" ),
            "Year-Month", FORMAT ( [Date], "YYYY-MM" ),
            "Financial Year",
                YEAR (
                    DATE ( YEAR ( [Date] )
                        - IF ( MONTH ( [Date] ) < FinancialYearStartMonth, 1, 0 ), FinancialYearStartMonth, 1 )
                ) + 1,
            "Financial Month",
                MOD ( MONTH ( [Date] ) - FinancialYearStartMonth + 12, 12 ) + 1
        )
    

6 Replies

  • You are solving the wrong problem.  Calendars are immutable. There is no point in doing this in either Power Query or DAX.  Use an external, precomputed reference table.

  • Hi Jerzee 

     

    Different companies might have different ways of them deciding a financial year .

     

    for the below example of calculated table you can create a fiscal calender staring from April 

     

    Date Table = 
    ADDCOLUMNS(
        CALENDAR(DATE(2015,4,1), DATE(2030,3,31)),
    
        -- Fiscal Year
        "Fiscal Year", 
            "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00"),
    
        -- Fiscal Year-Quarter
        "Fiscal Year-Quarter", 
            "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00") & " " &
            "Q" & 
            SWITCH(
                TRUE(),
                MONTH([Date]) IN {4,5,6}, 1,
                MONTH([Date]) IN {7,8,9}, 2,
                MONTH([Date]) IN {10,11,12}, 3,
                MONTH([Date]) IN {1,2,3}, 4
            ),
    
        -- Month Name (Text)
        "Month", FORMAT([Date], "MMMM"),
    
        -- Actual Month Number (Calendar Month 1 = Jan)
        "Actual Month Number", MONTH([Date]),
    
        -- Fiscal Month Number (April = 1, May = 2, ..., March = 12)
        "Fiscal Month Number", MOD(MONTH([Date]) + 8, 12) + 1
    )

     

  • v-kathullac's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support

    Thanks kushanNa lbendlin  for Addressing the issue.

     

    Hi Jerzee ,
    we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

    Regards,

    Chaithanya.

  • Hi Jerzee 

     

    Try the sampe DAX calculated table below

    CalendarTable =
    VAR FinancialYearStartMonth = 7 -- set your financial year start month here (e.g., 7 = July)
    RETURN
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2030, 12, 31 ) ),
            "Year", YEAR ( [Date] ),
            "Month", MONTH ( [Date] ),
            "Month Name", FORMAT ( [Date], "MMMM" ),
            "Year-Month", FORMAT ( [Date], "YYYY-MM" ),
            "Financial Year",
                YEAR (
                    DATE ( YEAR ( [Date] )
                        - IF ( MONTH ( [Date] ) < FinancialYearStartMonth, 1, 0 ), FinancialYearStartMonth, 1 )
                ) + 1,
            "Financial Month",
                MOD ( MONTH ( [Date] ) - FinancialYearStartMonth + 12, 12 ) + 1
        )
    
  • v-kathullac's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support

    Hi @Jerzee ,
    we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

    Regards,

    Chaithanya.

  • v-kathullac's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support

    Hi @Jerzee ,
    we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

    Regards,

    Chaithanya.