topic Re: Break down Running Total into separate cases in DAX Commands and Tips

Break down Running Total into separate cases

Anonymous — Tue, 21 Jul 2020 13:42:37 GMT

Hello Everybody,

I've seen lots of explanations to create a running/accumulating total, however the raw data I have begins with a running total and I would like break this down to the value added on. Simple enough in Excel, I believe, but I would rather do it in BI.

I am looking at California's Testing rate for COVID-19 and would like to create a Calculated Column called DailyNewTests:

Which is to say, the first value would be 6291, the second would be 11487-6291 = 5196, the third would be 12528 - 11487 = 1041 and so on..

Would anyone be able to help me? Much appreciated!

Re: Break down Running Total into separate cases

camargos88 — Tue, 21 Jul 2020 14:09:44 GMT

Hi Anonymous ,

Try this code for a calculated column:

Column = VAR _previousDate = CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[Date] < EARLIER('Table'[Date]))) RETURN 'Table'[Values] - CALCULATE(SUM('Table'[Values]), FILTER('Table', 'Table'[Date] = _previousDate))

Re: Break down Running Total into separate cases

amitchandak — Tue, 21 Jul 2020 14:35:56 GMT

Anonymous ,

If you have non continous date

column =
var _last_date =maxx(filter(Table,Table[Date] <earlier(Table[Date])),Table[Date])
return Table[accumulated total]-maxx(filter(Table,Table[Date] =_last_date),Table[Date])

continuous Date

Total = Table[accumulated total]-maxx(filter(Table,Table[Date] =earlier(Table[Date])),Table[Date])

Refer this to have measures https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9