Forum Discussion

Mounika1's avatar
Mounika1
Frequent Visitor
9 years ago
Solved

Rolling Sum for a Measure

I have created a measure using columns. I have to create the rolling sum for that measure.

 

TTM-Receipts $ = CALCULATE(SUM(Purchases[RM $ Receipts]),DATESINPERIOD(Purchases[.Calendar Date])

 

Using the above formula we can only create rolling sum for a column. But i wanted it for a measure

 

Show and Tell
  • Anonymous's avatar
    Anonymous
    9 years ago

    Mounika1,

    Create measure using the DAX below.

    Running Sum = SUMX(FILTER(ALLSELECTED(Excel[Time Period]),Excel[Time Period]<=MAX(Excel[Time Period])),[Measure])

    Regards,
    Lydia

     

7 Replies

  • sumit4732's avatar
    sumit4732
    Advocate II
    9 years ago

    Hi Mounika1,

     

    We ussually use DATESBETWEEN function in Calculate to get values for rolling period.
    If you can provide some sample data and exact problem that you are trying to solve, it will help 

     

    Thanks,

    Sumit

    • Mounika1's avatar
      Mounika1
      Frequent Visitor
      9 years ago

      I have measure created for some time periods based on the max date. Now for that Measure i have to create the rolling Sum. 

      Below is the formula used to create the measure.

       

      Measure = calculate(sum(Excel[Value]), Filter(ALL(Excel[DAY]),Excel[DAY]=Final[MaxDate]))/1000 -0.01*(calculate(sum(weights[weight])))* calculate(sum(Excel[Value]), Filter(ALL(Excel),Excel[PERIOD]=calculate(max(Excel[PERIOD])).

      • sumit4732's avatar
        sumit4732
        Advocate II
        9 years ago

        Hi Mounika1,

         

        You are already using of max of date available, what do you mean by Rolling Sum?
        Can you please share table and excatly what you are loking for?

         

        Regards,

        Sumit