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

     

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