Financial Year Quarter

[M_SBS_6]

Hi, 

I need to create a Quarter Variable based on a month_start_no variable and a date_app_start if possible, please

I have several different financials years: which I think makes it more difficult:

Jan - Dec (Jan, Feb, Mar (Q1) Apr, May, Jun (Q2) Jul, Aug, Sep (Q3) Oct, Nov, Dec (Q4)

Mar - Feb (Mar, Apr, May (Q1) Jun, Jul, Aug (Q2), Sep, Oct, Nov (Q3) Dec, Jan, Feb (Q4)

Apr - Mar (Apr, May, Jun (Q1) Jul, Aug, Sep (Q2) Oct, Nov, Dec (Q3) Jan, Feb, Mar (Q4)

July - June (July, Aug, Sep (Q1) Oct, Nov, Dec (Q2) Jan, Feb, Mar (Q3) Apr, May, Jun (Q4)

Oct - Sept (Oct, Nov, Dec (Q1) Jan, Feb, Mar (Q2), Apr, May, Jun (Q3) July, Aug, Sep (Q4)

So I need somthing that will say, as an example - if month_start_no = 7 and date_app_start = 01/08/2022 then Q1

I have attached sample output that I hope to achieve.

app_date_start	month_name	Quarter
05/07/2022	7	Q1
12/12/2022	7	Q2
03/01/2023	7	Q3
17/01/2003	7	Q3
19/03/2023	7	Q3
30/06/2023	7	Q4
05/07/2022	4	Q2
12/12/2022	4	Q3
03/01/2023	4	Q3
17/01/2003	4	Q4
19/03/2023	4	Q4
05/07/2022	1	Q3
12/12/2022	1	Q4
03/01/2023	10	Q2
17/01/2003	10	Q2
19/03/2023	10	Q2
01/04/2022	10	Q3
07/09/2022	3	Q3
01/01/2023	3	Q4
25/02/2023	3	Q4
26/02/2023	3	Q4

[v-yinliw-msft · Accepted solution]

Hi @M_SBS_6 ,

And there is another mothed to solve this question:

New a table:

Financials =
DISTINCT (
    CROSSJOIN (
        SELECTCOLUMNS ( { 1, 3, 4, 7, 10 }, "Financial Start Month", [Value] ),
        SELECTCOLUMNS (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 1 ) ),
            "MonthName", FORMAT ( [Date], "mmm" ),
            "MonthNumber", MONTH ( [Date] )
        )
    )
)

Then new a column to the new table:

Financial Quarter =
"Q"
    & SWITCH (
        [Financial Start Month],
        1, QUARTER ( CONVERT ( [MonthName] & " 1", DATETIME ) ),
        3,
            VAR m1 = [MonthNumber] - 2
            RETURN
                QUARTER ( DATE ( 2022, IF ( m1 <= 0, m1 + 12, m1 ), 1 ) ),
        4,
            VAR m2 = [MonthNumber] - 3
            RETURN
                QUARTER ( DATE ( 2022, IF ( m2 <= 0, m2 + 12, m2 ), 1 ) ),
        7,
            VAR m3 = [MonthNumber] - 6
            RETURN
                QUARTER ( DATE ( 2022, IF ( m3 <= 0, m3 + 12, m3 ), 1 ) ),
        10,
            VAR m4 = [MonthNumber] - 9
            RETURN
                QUARTER ( DATE ( 2022, IF ( m4 <= 0, m4 + 12, m4 ), 1 ) )
    )

Then in the original table, please add the column:

Quarter =
LOOKUPVALUE (
    'Financials'[Financial Quarter],
    Financials[Financial Start Month], [month_name],
    Financials[MonthNumber], MONTH ( [app_date_start] )
)

The result is:

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yinliw-msft]

Hi @M_SBS_6 ,

You can try this method:

I create some new tables:

Use JAN as an example:

And I create 5 tables to select the first row.

The JAN and the TopJAN is an example that other tables are the same.

Then create a new column in the table:

Month = MONTH('Table'[app_date_start])

Then create Quarter column:

Quarter =

IF (

    'Table'[month_name] = MAX ( 'TopJAN'[Jan-Dec] ),

    CALCULATE (

        MAX ( 'JAN'[Quarter] ),

        FILTER ( 'JAN', 'Table'[Month] = 'JAN'[Month] )

    ),

    IF (

        'Table'[month_name] = MAX ( 'TopMAR'[Mar-Feb] ),

        CALCULATE (

            MAX ( 'MAR'[Quarter] ),

            FILTER ( 'MAR', 'Table'[Month] = 'MAR'[Month] )

        ),

        IF (

            'Table'[month_name] = MAX ( 'TopAPR'[Apr-Mar] ),

            CALCULATE (

                MAX ( 'APR'[Quarter] ),

                FILTER ( 'APR', 'Table'[Month] = 'APR'[Month] )

            ),

            IF (

                'Table'[month_name] = MAX ( 'TopJUL'[Jul-Jun] ),

                CALCULATE (

                    MAX ( 'JUL'[Quarter] ),

                    FILTER ( 'JUL', 'Table'[Month] = 'JUL'[Month] )

                ),

                IF (

                    'Table'[month_name] = MAX ( 'TopOCT'[Oct-Sep] ),

                    CALCULATE (

                        MAX ( 'OCT'[Quarter] ),

                        FILTER ( 'OCT', 'Table'[Month] = 'OCT'[Month] )

                    )

                )

            )

        )

    )

)

The result is:

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@M_SBS_6 , refer my blog can help

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1