Rolling 3 Month Formula result is the same as actual month

[matratus28]

Hi - I'm trying to calculate rolling 3 month sales using DAX in Power Pivot, but when I pull my measure into my pivot it just gives me the same value as for the actuals for that month. - can you please help?

I have an actuals table with a Total_Sales column.

I have created a Sales measure with - Sales:=SUM(t_actuals[Total_Sales])
Sales are at a product level so there are multiple rows for each week/month in the table.

The actuals table has a week column in YYYYWW format which is joined to a calendar table in the same format.
The calendar table has a YYYYMM column (for period) and a period start date in dd/mm/yyyy format

To try and create a rolling 3 month/period calculation I have written this but it gives me the actuals for each period and not the 3 month average. 

Rolling 3 Month Sales:=
CALCULATE([Sales],

DATESINPERIOD(

t_calendar[Period start date],

MAX(t_calendar[Period start date]),

-3,

MONTH

)

)

Pivot result:

Long period	Period start date	Sales	Rolling 3 Month Sales
202407	30/09/2023	£1,850,155	£1,850,155
202408	28/10/2023	£7,950,967	£7,950,967
202409	25/11/2023	£11,401,870	£11,401,870
202410	30/12/2023	£7,160,039	£7,160,039
202411	27/01/2024	£9,384,733	£9,384,733
202412	24/02/2024	£11,124,963	£11,124,963
202501	01/04/2024	£9,041,011	£9,041,011
202502	27/04/2024	£10,867,145	£10,867,145
202503	01/06/2024	£7,907,225	£7,907,225
202504	29/06/2024	£8,902,636	£8,902,636
202505	27/07/2024	£10,833,004	£10,833,004
202506	31/08/2024	£8,693,422	£8,693,422
202507	28/09/2024	£8,417,511	£8,417,511

[v-nuoc-msft]

Hi @matratus28 

Thank you very much DataNinja777 for your prompt reply.

Pls try this:

Rolling 3 Month Sales :=
CALCULATE(
    [Sales],
    DATESINPERIOD(
        t_calendar[Period start date],
        MAX(t_calendar[Period start date]),
        -3,
        MONTH
    ),
    REMOVEFILTERS(t_calendar[Long Period])  // This line removes the filter context from Long Period
)

Regards,

Nono Chen

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

[matratus28]

Thanks for your reply - sorry I haven't responded but I've been away for a few days - I'll test this as soon as I get a chance and let you know how I get on.

Thanks

[matratus28]

Update to this - the measure works correctly if I remove "Long Period" from the pivot and just have Period start date in the pivot.
Thanks for looking 😄

[DataNinja777]

@matratus28 ,

It sounds like the issue was related to filter context in your pivot table. When you included "Long Period" (likely referring to a higher-level grouping like YYYYMM), it might have affected the way the DATESINPERIOD function evaluated the rolling 3-month window.

Here’s how you can refine the measure to ensure it works regardless of which fields are used in the pivot:

Rolling 3 Month Sales :=
CALCULATE(
    [Sales],
    DATESINPERIOD(
        t_calendar[Period start date],
        MAX(t_calendar[Period start date]),
        -3,
        MONTH
    ),
    ALL(t_calendar[YYYYMM])  -- Ignores Long Period context
)

You can try using the ALL function to remove the influence of "Long Period" from the filter context, ensuring your measure behaves consistently across different fields in the pivot.

Best regards,

[matratus28]

Hi DataNinja777 - thanks for taking the time to reply. I've been on holiday for the last week and so hadn't seen this. I'll hopefully get chance to test it later in the week and I'll let you know how I get on.

Thanks