How to Aggregate based on DateDiff Column?

[efowler]

Hi, I am trying to show a line chart in Power BI based on a DateDiff column.    The example in Excel is attached as well as how it currently looks in Power BI.   Any suggestions on how I can do a Sum (running total) of the value then show the line graphs as a cummulative total of the values called R1000?  

X Axis = Date Diff "MIS" 1 through 50

Y Axis = Cumulative total "R1000" based on the prior Date Diff value

Legend = 2018, 2019, 2020Power BI (Bad)MS Excel (Good)

[richbenmintz]

Hi @efowler ,

Below you will find an example of a running total calculation, that should get you going in the right direction.

Cummulative R1000 = 
	Calcluate(
		SUM('table'[R1000]), 
			filter(
                                allselected('table'[MIS (Failure)]), 'table'[MIS (Failure)]<=max('table'[MIS (Failure)])
                     )
)

Hope that helps,

[efowler]

Hi, thanks..    R1000 is a measure.  Does that make a difference?   I can not find the 'table'[R1000], I believe that is because it is a measure?

[richbenmintz]

Hi @efowler ,

'table' is a place holder as i do not know the name of your table, for the measure name please leave out the table name

[efowler]

I can't add my table because the data I am trying to create the running total on is a measure.  The MIS is a calculated column 

[richbenmintz]

Hi @efowler ,

Please provide a sample pbix file, will make it much easier to craft a working measure for you

[efowler]

Hi, the sample .pbix file is attached.   

Link to .pbix file 

[richbenmintz · Accepted solution]

Hi here is what I think gets you what you are looking for

Cummulative R1000 = 
	CALCULATE(
		[R1000], 
			filter(
                                allselected('warranty'[MIS]), 'warranty'[MIS]<=max('warranty'[MIS])
                     )
)

Also find the updated pbix