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

     

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

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