Forum Discussion

OR's avatar
OR
Regular Visitor
7 years ago

Not Your Typical 12 Month Rolling Sum Problem

Hi! I hit a wall and need your help.  Here is the situation:

 

I have a sales dataset for several years: 2016 - 2020, with data reported in monthly buckets.

I need to achieve the following: for year 2018 display (in a table and in a chart, filtered to year 2018) on a monthly basis the trailing (rolling) 12 month sum of sales. This means that for January 2018 - I would display the sum of sales from February 2017 through January 2018. For February 2018 - I would display the sum of sales from March 2017 through February 2018, and so on.

 

When applying the typical:

Calculate ( sum[sales], datesinperiod(...)) approach, the following happens:

 

For January 2018, I get not the sales from February 2017 through January 2018, but just the sales in the month of January, 2018. For February 2018, I get not the sales from March 2017 through February 2018, but just the sales in the month of January and February of 2018, and so on. Finaly by December 2018, I get a proper 12 month rolling sum of sales, ranging January 2018 to December 2018.

 

What I need to accomplish is, despite filtering the visual (table, or chart) for 2018, I need the 12 month rolling sum measure to look back, outside the bounds of the filtered (for 2018) chart or table, and retrieve the appropriate 12 months, and produce their sum.

 

Any help in achieving this would be greatly appreciated. Please help!

 

 

 

 

Need Help

5 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi OR

     

    Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

     

    Reagrds,

    Cherie

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      7 years ago

      Anonymous v-cherch-msft

       

      I am also interested in this subject because this is also useful in my job and can reduct the DAX I wrote.

       

      Measure = 
VAR LDATE=MAX('Sheet1'[Month])
var FDATE  = EDATE(LDATE,-3)
RETURN 
CALCULATE(SUM(Sheet1[sales]),DATESBETWEEN('Sheet1'[Month],FDATE,LDATE))

      Above is the coding I used by reading the subject that CheenuSing recommended. That is very helpful.

       

      Here is my question. If my data starts on 2018/1/1, then for Jan, Feb and Mar, we don't have completed last four month's value. 

      So is it possible to modify the coding to make the results only show from Apr 2018?

       

      Thanks in advance.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi ryan_mayu,

         

        I am assuming

        a) the data always starts from 01/01/2018 - Jan 2018.

        b) Beyond Apr 2018 how the sum of previous three months - as you already doing.

            By this logic going forward - May 2018 will show sum of Feb to Apr 2018,.., Jan 2019 will show sum of Oct to Dec 2018.

         

        I think the following tweak to the measure should work

         

        Measure =

        VAR FirstMonth = Min('Sheet1'[Month] )  / * get the first month of the data set */
        VAR LDATE=MAX('Sheet1'[Month])
        var FDATE  = EDATE(LDATE,-3)

        VAR Is3MonthsComplete = LDATE - FirstMonth    /* This gives the difference between First and Latest Month in the dataset */

        RETURN

        If  (Is3MonthsComplete > 3 ,
                                            CALCULATE(SUM(Sheet1[sales]),DATESBETWEEN('Sheet1'[Month],FDATE,LDATE))

        , Blank())

        /* If the difference is >3 months then calculate the sum of last 3 months, else show as blank */

         

        Let me know if this works for you.

         

        Cheers

         

        CheenuSing