Forum Discussion

Medic7653's avatar
Medic7653
Regular Visitor
8 years ago
Solved

6 Month Moving Sum

New member and first time poster.   After a lot of research and reading, I am unable to resolve my issue.  I can easily create a moving sum of my volume, but I need months 1 - 5 to remain blank and...
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Medic7653,

     

    Believe that you are using a filter or something similar to get the previous 6 months however taking into account the data the first rows also are consider in the calculations althoung for those specific rows it's not getting the full 6 months since they don't have enough rows. You need to do something like this:

     

    Moving 6 Months Sum =
VAR Month_selected =
    MAX ( 'Fact'[Month #] )
RETURN
    IF (
        Month_selected < 6;
        BLANK ();
        CALCULATE (
            SUM ( 'Fact'[Volume] );
            FILTER (
                ALL ( 'Fact'[Month #]; 'Fact'[Year]; 'Fact'[Month] );
                'Fact'[Month #]
                    >= Month_selected - 5
                    && 'Fact'[Month #] <= Month_selected
            )
        )
    )

    Regards

    MFelix

tinkertrust's avatar
tinkertrust
Frequent Visitor
8 years ago

Hi, I had a similar scenario and found this to work well: 

 

6-Mo Rolling Profit:= CALCULATE([Profit], DATESINPERIOD(Calendar_Lookup[date], MAX(Calendar_Lookup[date]), -6, MONTH))

6-Mo Rolling Avg Profit = [6-Month Rolling Profit] /
CALCULATE(DISTINCTCOUNT(Calendar_Lookup[Year_Month]), DATESINPERIOD(Calendar_Lookup[date], LASTDATE(Calendar_Lookup[date]), -6, MONTH))

 

This calcualtes a 6 month rolling sum first and then worksout the average. The second formula should returns the average of the first month alone, then the first two months, and finally the 3-month period for the remaining months.

 

Hope it helps.