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,

 

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

    • Mounika1's avatar
      Mounika1
      Frequent Visitor
      9 years ago

      Hi Anonymous

      T'day value_1 = calculate(sum(Excel[Expo]), Filter(ALL(Excel[DAY]),Excel[DAY]=Final[Date]))/1000

       

      T'day value_2 = calculate(sum(Expo[Expo]), Filter(ALL(Excel),Excel[Time Period]=calculate(max(Excel[Time Period]), Filter(ALL(Excel), Excel[Time Period] < max(Excel[Time Period]))) && Excel[Time Period]=Final[Date]))/1000

       

      T'day value_3 = (calculate(sum(Excel[Expo]), Filter(ALL(Excel),Excel[Time Period]=calculate(max(Excel[Time Period]), Filter(ALL(Excel), Excel[Time Period] < calculate(max(Excel[Time Period]), Filter(All(Excel), Excel[Time Period] < max(Excel[Time Period]))))) && Excel[DAY]=Final[Date]))/1000)

       

      Measure =  calculate(sum(Excel[Expo]), Filter(ALL(Excel[DAY]),Excel[DAY]=Final[Date]))/1000 -0.01*(calculate(sum(Values[Value_1])))* calculate(sum(Excel[Expo]), Filter(ALL(Excel),Excel[Time Period]=calculate(max(Excel[Time Period]), Filter(ALL(Excel), Excel[Time Period] < max(Excel[Time Period]))) && Excel[DAY]=Final[Date]))/1000 - 0.01*(calculate(sum(Values[Value_2])))*(calculate(sum(Excel[Expo]), Filter(ALL(Excel),Excel[Time Period]=calculate(max(Excel[Time Period]), Filter(ALL(Excel), Excel[Time Period] < calculate(max(Excel[Time Period]), Filter(All(Excel), Excel[Time Period] < max(Excel[Time Period]))))) && Excel[DAY]=Final[Date]))/1000)

       

      above are the formulae used to create the fields in the attached image.

      Now i want the Running Sum of the measure corresponding to Time Period as below.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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