Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative Measure

Hello All,   I'm trying to create a cumulative measure that is based off two other measures.  The normal DAX pattern that I use for Cumulative totals is not working.    I started with this measur...
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Does this work:

    CALCULATE(
	SUMX(
		VALUES(Dates[Date]),
		[Daily Forecast]
		
	),
 	FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX(Dates[Date]) ))
)
bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

I think the issue is that the base measure "90 Days Complete" is always returning a value for "today".


If you modify that to give 90 days from the latest date in the filter context it should get a bit further.

 

I would also have thought for a cumulative total you'd need a SUMX over dates in your last measure but I might be mis understanding what output you're wanting.

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for weighing in bsdobbs!

 

I'm trying to dynamically calculate the total story points completed, going back 90 days from today.  I'm then trying to take that value to calculate the avg. story points completed by day, over the past 90 days.  Third, I'm trying to then take the daily avg. value to create a cumulative sum of the daily avg.  

 

Here's an example of the result I'm trying to achieve:

 

 

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Does this work:

    CALCULATE(
	SUMX(
		VALUES(Dates[Date]),
		[Daily Forecast]
		
	),
 	FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX(Dates[Date]) ))
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It works like a dream!  I hereby declare you as my hero of the day!

       

      Thank you!