Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Need DAX for Fiscal Month Column

Hello, 2 requests for 2 different source please.

 

1) I have a Date table created this way:

Date = ADDCOLUMNS(CALENDAR(DATE(2021,6,30),DATE(2024,6,30)),"Fiscal Month",FORMAT([Date],"mmmm, yyyy")).
The Fiscal Month output looks like this: July, 2023, August, 2023, etc.
I would like to create a Fiscal Month column so that July, 2023, August, 2023, September, 2023 falls under FY24Q1; October, 2023, November, 2023, December, 2023 falls under FY24Q2, etc.
In other words, the Fiscal Year starts on month of July.
I have to do this for the time period of July, 2018 all the way through June, 2024.
 
2) I have another table that also has a Fiscal Month column that the Fiscal Month values need to be grouped into quarters in the same way as #1 above. 
In general, how can I group months into quarters, such that 1st quarter of the year starts in July, and 4th quarter ends in June of the following year?

THANK YOU!
Need Help

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    DAX Pattern has nice formulas for this

    https://www.daxpatterns.com/custom-time-related-calculations/

     

    In particular,

        VAR FirstFiscalMonth = 3
   [...]
    VAR Fyr =                            -- Fiscal Year Number
        Yr + 1 * ( FirstFiscalMonth > 1 && Mn >= FirstFiscalMonth )
    VAR Fmn =                            -- Fiscal Month Number (1-12)
        Mn - FirstFiscalMonth + 1 + 12 * (Mn < FirstFiscalMonth)
    VAR Fqrn =                           -- Fiscal Quarter (string)
        ROUNDUP ( Fmn / 3, 0 )
    [...]
    "Fiscal Year Quarter", "F" & Fqr & "-" & Fyr,
    [...]

     

    If you want FY00Q0 format istsead of FQ0-0000 format, then try this

     

    "Fiscal Year Quarter", "FY" & MOD ( Fyr, 100 ) & "Q" & Fqr