Forum Discussion

wyanjaspew's avatar
wyanjaspew
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Rolling values for 3 months

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, bu...
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi wyanjaspew 

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

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

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's avatar
    wyanjaspew
    Icon for Advocate I rankAdvocate I
    1 year ago

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

     

    • rohit1991's avatar
      rohit1991
      Icon for Super User rankSuper User
      1 year ago

      Hi wyanjaspew 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.



      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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