topic Re: Creation of a running total with change between two data series in DAX Commands and Tips

Creation of a running total with change between two data series

Anonymous — Tue, 27 Dec 2022 11:41:31 GMT

Good morning,
I want to create a running total that changes from data series 1 to data series 2 at a certain date.
As an example, a forecast could be used, which adds up the actual costs until a defined date and then continues to add up the planned costs. This should result in an S-curve, which shows the actual values with a solid line up to this defined point in time and then indicates the planned values with a dashed line.

How could I implement this?

Love greetings 🙂

Re: Creation of a running total with change between two data series

amitchandak — Tue, 27 Dec 2022 11:52:28 GMT

Anonymous , if you use date table the both series will have their won running total

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

You can also consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Re: Creation of a running total with change between two data series

Anonymous — Tue, 27 Dec 2022 12:22:55 GMT

Thanks for your message amitchandak . I want to create a running total from the data, which should sum the values of one data series up to a certain date and then continue to sum the values of another data series. Before the change, the second data series should be ignored and after the change, the first data series should be ignored to avoid a gap or jump in the line.

Re: Creation of a running total with change between two data series

FreemanZ — Wed, 28 Dec 2022 02:47:16 GMT

hi Anonymous

for your reference:

https://www.daxpatterns.com/budget/