Rolling values for 3 months

[wyanjaspew]

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

[v-sshirivolu]

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

[rohit1991]

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.

[wyanjaspew]

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

[v-sshirivolu]

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.

[v-sshirivolu]

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

[v-sshirivolu]

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.

[rohit1991]

Hi @wyanjaspew 

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

[MohamedFowzan1]

Hi @wyanjaspew 

Could you check if this works:

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

[wyanjaspew]

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