Cumulative Measure

[Benx]

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 measure to capture completed story points over the past 90 days:

90 Day Completed =

VAR Today1 = TODAY()

VAR Result =

CALCULATE ([Completed Points],

          FILTER( ALL(Dates),

                 Dates[Date] > Today1 - 90 &&

                 Dates[Date] <= Today1))

RETURN

Result

Next, I tried to calculate the average daily story points completed with the following measure:

Daily Forecast = [90 Day Completed]/90

Finally, I tried to calculate the cumulative daily completed story points here:

**bleep** Daily Forecast =

CALCULATE([Daily Forecast],

         FILTER( ALLSELECTED( Dates),

                Dates[Date] <= MAX(Dates[Date]) ))

As you can see below, the first two measures appear to be working as plannned. However, my **bleep** Daily Forecast measure is not prodicing the desires result.  

Can anyone suggest a solution to produce an actual cumulative result for the daily story points completed?

Thank you for the help!!!

[bcdobbs]

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.

[Benx]

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 · Accepted solution]

Does this work:

CALCULATE(
	SUMX(
		VALUES(Dates[Date]),
		[Daily Forecast]
		
	),
 	FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX(Dates[Date]) ))
)

[Benx]

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

Thank you!