topic Re: Rolling values for 3 months in DAX Commands and Tips

Rolling values for 3 months

wyanjaspew — Fri, 08 Aug 2025 05:44:15 GMT

Hi Guys,

Could you help me configure this? I'm trying to display the rolling 3-month period like below.

I'm trying to incorporate the code into a measure within my actual dataset, but it doesn't seem to be functioning properly.

Actual dataset

Re: Rolling values for 3 months

MohamedFowzan1 — Fri, 08 Aug 2025 05:55:49 GMT

Hi wyanjaspew

Could you check if this works:

Rolling 3 Month Total = CALCULATE( SUM(FactTable[ValueColumn]), DATESINPERIOD( 'DateTable'[Date], MAX('DateTable'[Date]), -3, MONTH ) )

Re: Rolling values for 3 months

rohit1991 — Fri, 08 Aug 2025 06:32:33 GMT

Hi wyanjaspew

1. Create a proper Date table

In Modeling >> New table:

Date = ADDCOLUMNS ( CALENDAR ( MIN ( FactSales[Date] ), MAX ( FactSales[Date] ) ), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "MMM" ), "YearMonth", FORMAT ( [Date], "YYYY-MM" ) )

Mark as date table (Modeling >> Mark as date table >> select [Date]).
Create a relationship: Date[Date] >> FactSales[Date] (single direction).

2. Base measure

Amount = SUM ( FactSales[Amount] )

3. Rolling 3-month sum (includes current month + prior 2)

Rolling 3M (Sum) = CALCULATE ( [Amount], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) )

4. Rolling 3-month average

Rolling 3M (Avg)= VAR Sum3M = CALCULATE ( [Amount], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) ) VAR MonthsPresent = CALCULATE ( DISTINCTCOUNT ( 'Date'[YearMonth] ), DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) ) RETURN DIVIDE ( Sum3M, MonthsPresent )

5. Build the visual

Use a Table or Matrix.
Put Date[YearMonth] (or Date[Date] set to Month), then add:
- [Amount]
- Rolling 3M (Sum) (and/or Rolling 3M (Avg))

You should see the DAX measure match the Expected_Rolling3M_Sum in the Excel.

Re: Rolling values for 3 months

Anonymous — Fri, 08 Aug 2025 10:16:17 GMT

Hi wyanjaspew ,

I would also take a moment to thank rohit1991 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

Re: Rolling values for 3 months

wyanjaspew — Fri, 08 Aug 2025 14:33:19 GMT

When I apply this code, it only display the same numbers (sum) of my serviceid's

Re: Rolling values for 3 months

wyanjaspew — Fri, 08 Aug 2025 14:35:41 GMT

When I apply this code, it only display the same numbers (sum) of my serviceid's

Re: Rolling values for 3 months

rohit1991 — Mon, 11 Aug 2025 06:05:13 GMT

Hi wyanjaspew

Could you please check this:
In Model view, create a relationship from Date Table[Date] (1) to Fact Sales[Date] (*)

Re: Rolling values for 3 months

Anonymous — Wed, 13 Aug 2025 07:05:57 GMT

Hi wyanjaspew ,

Seeing repeated values usually suggests that your rolling measure isn't getting the right filter context from the model. Here are some things to check:

Date Table

Make sure you have a proper Date table created with your DAX.

Set it as a Date table in your model.

Confirm it covers your entire data range.

Model Relationship

In Model view, check for a single-directional relationship from Date[Date] to FactSales[Date].

Visual Context

Use a table or matrix that includes serviceid with your rolling measures.

Verify the service-level filter is applied so the measure recalculates for each service.

Quick Test

Add a slicer for serviceid to see if the rolling measure updates.

If it doesn't, there's likely a relationship or context issue.

With these steps, your rolling 3-month DAX should provide different values for each serviceid as expected.

Re: Rolling values for 3 months

Anonymous — Mon, 18 Aug 2025 05:16:25 GMT

Hi wyanjaspew ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

Re: Rolling values for 3 months

Anonymous — Mon, 25 Aug 2025 05:45:18 GMT

Hi wyanjaspew ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.