Using Year-Month Slicer Calculating the Avearge 3 month values of Quarterly values

[anusha_2023]

I am trying to calculate the Average values of each Quarter and following rolling 8 Quarters. If 2024-11 is selected then 8 quarters need to show from 2023-Q1 to 2024-Q4 as below.

Now need to adjust this below DAX according to a special boundary case where Starting Quarters I have managed to adjust with NumberOfMonths Variable. End Quarter values I need to adjust if 2024-11 is selected in this case 2024-Q3 will be same with months values 2024-07, 2024-08, 2024-09 but end Quarter 2024-Q4 should also show 3 months data with 2024-09, 2024-10, 2024-11 instead of showing only 2024-10 and 2024-11. Can I adjust this calculation for end Quarter dynamically when the selected month is not MONTH(Reference_Date) IN {3, 6, 9, 12} then I need to adjust. Is it possible using DAX or I need to adjust DATE tabel as well. Please suggest me further.

ARR = VAR Reference_Date = MAX('Date'[Date])

VAR NumberofMonths =

SWITCH(

    TRUE(),

    MONTH(Reference_Date) IN {1, 4, 7, 10}, 22,

    MONTH(Reference_Date) IN {2, 5, 8, 11}, 23,

    MONTH(Reference_Date) IN {3, 6, 9, 12}, 24,

    BLANK() -- Default case

)

VAR RollingQuarters = DATESINPERIOD(

        'Recurring date'[Date],

        Reference_Date,

        -NumberofMonths,

        MONTH

    )

RETURN

CALCULATE(

    ([Amount (Daily)] / 3) * 12 * 1000,

    KPIsView[Type] = "Actual",

    REMOVEFILTERS('Date'),

    KEEPFILTERS(RollingQuarters),

    USERELATIONSHIP('Date'[Date], 'Recurring date'[Date]),

    'AccountKPIsReportView'[AccountGroup] IN {331, 341, 381}

)

[johnt75]

Add a column on to your date table which stores the date of the end of quarter. I think you can use

End of quarter = ENDOFQUARTER( 'Date'[Date] )

You could then create a measure like

ARR =
VAR ReferenceDate =
    MAX ( 'Date'[End of quarter] )
VAR DatesToUse =
    CALCULATETABLE (
        DATEADD ( 'Date'[Date], -8, QUARTER ),
        TREATAS ( { ReferenceDate }, 'Date'[Date] )
    )
VAR Result =
    CALCULATE (
        ( [Amount (Daily)] / 3 ) * 12 * 1000,
        KPIsView[Type] = "Actual",
        KEEPFILTERS ( DatesToUse ),
        'AccountKPIsReportView'[AccountGroup] IN { 331, 341, 381 }
    )
RETURN
    Result

[anusha_2023]

Thank you very much for your reply. In my case I need to adjust only final quarter with showing 8 Quarters data all time. If 2024-11 year-month slicer is selected then in the bar graph for 2024-Q4 need to show Sep,Oct,Nov last three months need to show for the last quarter while 2024-Q3 still need to show Jul,Aug,Sep. Kind of Sep values need to be repeated in the both Quarters.

[Anonymous]

Hi @anusha_2023 ,

I think your issue is based on your data model. I suggest you to add an unrelated Year Quarter table for X axis in your chart. 

Please share a sample file with us and show us a screenshot with the result you want. This will make it easier to find the solution.

Best Regards,
Rico Zhou

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

[anusha_2023]

Thanks for the reply.

I made a sample visual as below. Of 2023-03 selected from slicer then in the Quarter visual 2023-Q1 has 2023-01, 2023-02, and 2023-03 months that is normal.

Now I need to modify to achive the speacial cases if 2023-04 is selected from the slicer then 2023-Q2 Contains 2023-02, 2023-04,2023-05. I have shared the sample from the google drive. Please suggest me further steps  https://drive.google.com/file/d/10UUwpXEz_SaHsOwvVvVe91E-2cn1S_6J/view?usp=drive_link 

[Anonymous]

Hi @anusha_2023 ,

It seems that we don't have permission to your data source link.

Please approve our request or you can share a new link with public permission to us.

Best Regards,
Rico Zhou

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

[anusha_2023]

I have granted the permission to access the file

[Anonymous · Accepted solution]

Hi @anusha_2023 ,

Please try code as below to update your measure.

Measure = 
VAR Reference_Date = MAX('Date'[Date])

VAR NumberofMonths =
SWITCH(
    TRUE(),
    MONTH(Reference_Date) IN {1, 4, 7, 10}, 22,
    MONTH(Reference_Date) IN {2, 5, 8, 11}, 23,
    MONTH(Reference_Date) IN {3, 6, 9, 12}, 24,
    BLANK() -- Default case
)

VAR RollingMonths = 
    DATESINPERIOD(
        'Recurring date'[Date],
        Reference_Date,
        -NumberofMonths,
        MONTH
    )

VAR _R1 = 
    CALCULATE(
        SUM(BalanceSheetSample[Amount]), 
        REMOVEFILTERS('Date'),
        KEEPFILTERS(RollingMonths),
        USERELATIONSHIP('Date'[Date], 'Recurring date'[Date])
    )

VAR RollingMonths2 = 
    DATESINPERIOD(
        'Recurring date'[Date],
        Reference_Date,
        -3,
        MONTH
    )
VAR _R2 = 
    CALCULATE(
        SUM(BalanceSheetSample[Amount]), 
        REMOVEFILTERS('Date'),
        KEEPFILTERS(RollingMonths2),
        USERELATIONSHIP('Date'[Date], 'Recurring date'[Date]),
        ALLSELECTED('Recurring Date')
    )
RETURN
IF(MAX('Recurring Date'[YearQuarter]) = FORMAT(Reference_Date,"YYYY")&"/Q"&FORMAT(Reference_Date,"Q"),_R2,_R1)

Result is as below.

Best Regards,
Rico Zhou

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

[anusha_2023]

Perfect Answer. Thank you very much. It worked lika a charm that I am struugling to solve in so many ways

[johnt75]

I don't think that you can duplicate axis values in the same visual, I think you would need to use separate charts for that.