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 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's avatar
    bcdobbs
    4 years ago

    Does this work:

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

4 Replies

  • 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]) ))
)