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 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))

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.

5 Replies

  • 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 -

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    • rajannaap's avatar
      rajannaap
      Regular Visitor
      7 years ago

      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