DAX formula does not calculate the last 3 month of the previous year

[Anonymous]

Hi,

I have a sales data and I want to calculate average sales of last 3 months before the current month. For example, if the current month is may of 2023, then the measure should calculate the average of april, march, and february. Although I have already had a measure which works well till now, because the year changed now the measure does not calculate the average of last 3 months. I mean, if the current month is january of 2024, then the measure does not calculate the average of december, november, october. The dax formula is the following: 

CALCULATE(

    AVERAGEX(

        DATESINPERIOD(

            'calendar'[Date],

            EDATE(LASTDATE('calendar'[Date].[Date]), -1),

            -3,

            MONTH

        ),

        [sum_sales]

    ),

    ALL('STOCK DRAFT'[month]

))
I would be very happy if you can help me. It is so urgent... Thank you in advance.

[Anonymous · Accepted solution]

HI @Anonymous,

You can try to use the following measure formula if it suitable for your scenario:

formula =
VAR currDate =
    MAX ( 'calendar'[Date] )
RETURN
    AVERAGEX (
        SUMMARIZE (
            FILTER (
                ADDCOLUMNS (
                    ALLSELECTED ( 'Fact' ),
                    "Year", YEAR ( 'Fact'[Date] ),
                    "Month", MONTH ( 'Fact'[Date] )
                ),
                [Date]
                    >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
                    && [Date] < currDate
            ),
            [Year],
            [Month],
            "Total", [sum_sales]
        ),
        [Total]
    )

Regards,

Xiaoxin Sheng