Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Rolling 3 Month Sum

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 ...
Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    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.

mattbrice's avatar
mattbrice
Icon for Solution Sage rankSolution Sage
8 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

That worked!  Thanks for your help.  

  • nickchobotar's avatar
    nickchobotar
    Icon for Skilled Sharer rankSkilled Sharer
    8 years ago

    Anonymous

     

     

    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 -