Rolling values for 3 months

Hi wyanjaspew 

Could you check if this works:

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

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.

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