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])   Us...
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

     

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

    • Mounika1's avatar
      Mounika1
      Frequent Visitor
      9 years ago

      Hi sumit4732,

       

      Below mentioned is the measure created using the above mentioned formula. For this table i want the rolling SUM/cummulative SUM.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Mounika1,

        You have date type field in your table, right? If so, please check if the following DAX returns your expected result.

        RunningSum = SUMX(FILTER(ALLSELECTED('Table'[DateColumn]),'Table'[DateColumn]<=MAX('Table'[DateColumn])),[Measure1])

        If you still have issues, please share sample data of Excel table, Final table and weights table for us to analyze.

        Regards,
        Lydia