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 then months 6 - n to populate with my moving sum values as illustrated below:

 

YearMonthVolumeMA-6MMonth #
1996Jul                  27,862 1
1996Aug                  25,485 2
1996Sep                  26,381 3
1996Oct                  37,516 4
1996Nov                  45,600 5
1996Dec                  45,240                 208,0846
1997Jan                  61,258                 241,4807
1997Feb                  38,484                 254,4798

 

Months 1 - 5 need to remain blank for subsequent calculations where I use SAMEPERIODLASTYEAR.

 

My introduction to DAX has been via the deep-end of the pool the past 5-wks, and I just haven't been able to put all the pieces together.  Additionally, I do have a calendar linking my data together.  Any guidance would be appreciated.  Thanks.

  • 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

9 Replies

  • Medic7653's avatar
    Medic7653
    Regular Visitor
    8 years ago

    Apologies.  Here's my base formula for my measure:

    Moving 6 Month Sum :=

    CALCULATE(

    SUM(Order_Details[Tot Sales]),

    DATESINPERIOD(DimDate[Date],

    LASTDATE(DimDate[Date]),

    -6,MONTH)

    )

    • OwenAuger's avatar
      OwenAuger
      Super User
      8 years ago

      Hi Medic7653

       

      Just double-checking - do you want your Moving Sum measure to return blank when fewer than 6 months are present in Order_Details?

  • MFelix's avatar
    MFelix
    Super User
    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

    • Medic7653's avatar
      Medic7653
      Regular Visitor
      8 years ago

      MFelix-  Thanks for your reply.  Forgive me,  but Month # was added to illustratewhen I wanted the mvoing sum to begin.  It is not part of the data set.  My apologies as this is my first post.  The only data fields in my data set are: Year, Month, Volume and MA-6M.

      I was, however, able to learn quite a lot from your example.

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