Rolling 3 Month Sum

[jfiorillo]

I am using the formula below to calculate a rolling 3 month total but I want to exclude the current month. So for April I want to sum up Jan - March.  Currently the formula sums Feb - Apr. How can I modify the formula.  Thanks!!!

Jan      150

Feb     200

Mar     300

Apr     400

Rolling 3 Month Total = CALCULATE(KPI_Finance_Actual_Budget[Actual Profit],DATESINPERIOD(KPI_Finance_Actual_Budget[FullDate],LASTDATE(KPI_Finance_Actual_Budget[FullDate]),-3, MONTH))

[aznariy]

What it KPI_Finance_Actual_Budget[Actual Profit] in your original question? 

[rajannaap]

Hi ,

i need simillar help on my data

i have colums like 

Period        divsion   segment    net

201501          a              1              12332

201502          b              2              21233

..                    ...             ....               ......

201812          a              2              312333

I need to calcuate avg  net value (from 201701 to 201712)  for each division and segment by skipping recent 12 periods say (201801 to 201812)

your help would be appreciated.

Thanks

[mattbrice · Accepted solution]

Maybe this:

Rolling 3 Month Total =
CALCULATE (
    KPI_Finance_Actual_Budget[Actual Profit],
    DATESINPERIOD (
        KPI_Finance_Actual_Budget[FullDate],
        LASTDATE ( PREVIOUSMONTH ( KPI_Finance_Actual_Budget[FullDate] ) ),
        -3,
        MONTH
    )
)

Although if "KPI_Finance_Actual_Budget[FullDate]" doesn't have a consecutive range of all dates you may get some strange results.  Best to use a separate calendar table.

[jfiorillo]

That worked!  Thanks for your help.  

[nickchobotar]

@jfiorillo

If you happen to have a problem with consecutive days , you can use the solution below leveraging month numbers in your calendar dimension.

= CALCULATE(
    [Sales Amount],
    FILTER(
        ALL('Date'),
        'Date'[Month Number] > MAX('Date'[Month Number]) - 4
               && 'Date'[Month Number] <= MAX('Date'[Month Number])-1
   ) , VALUES('Date'[Calendar Year])
)

Thanks, Nick -