Running Totals, YTD, Cumulative?

[kasiaw29]

Hi community, 

I'm looking for clarity on DAX to get two measures. 

I have a DateDim table build and a Fact table that holds data on business savings (see dummy data below). It gives a project, saving start date, saving value and number of months savings are lasting for. 

I need to get a running total for each month, and then a running total of that total if that makes sense? 

See below: 

 

So Far I have this DAX to work out the blue line:

Cummulative Total Savings = CALCULATE([Cummulative savings], DATESYTD(DateDim[Date]))

Calc for [Cummulative savings] - 

Cummulative savings = CALCULATE(SUM('FactTable'[Cost Savings per Month]),

        FILTER('FactTable',

           'FactTable'[Saving Start Date] <= MAX(DateDim[Date])

           && ('FactTable'[Saving End Date] > MAX(DateDim[Date]))))

I need help getting the green line, I tried repeating what I did with cummulative total savings but it's not giving me the right figures. 

EDITED: 

Reason for above cumulative measure is becasue savings run for cerain periods of time, for example 7 months or 6 month or 8 months. So given the months of savings I was able to work out the end date. Then I run a sum of savings per month based on that and then did a cumulative measure to see each month, at the end of the motn how much I was saving. 

Now I just need a cumulative of that cumulative which is what green line shows in excel. 

Thanks,

Kasia 

[kasiaw29 · Accepted solution]

Reached a solution by following this thread :

https://community.powerbi.com/t5/Desktop/Advice-on-handling-cumulative-monthly-calculations-when-you... 

[amitchandak]

@kasiaw29 , first row seems monthly total without any other filter then time

like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

or

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]),filter(allselected(Date), Date[Month year] = Max(date[Month year])))

Cummulative  example with and without date table

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

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

[kasiaw29]

Not quite what I'm after. I already have a cumulative total that works ok, I'm after cumulative of that cumulative if it makes sense? 

Thanks! 

[amitchandak]

@kasiaw29 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[kasiaw29]

@amitchandak 🙂

Tried to share but it wouldn't let me. Here's all I have: 

BI Project ID	Saving Start Date	Cost Savings per Month	Months Of Saving
BI_02	22/01/2021	200	11
BI_07	20/05/2021	900	7
BI_04	03/05/2021	1,200.00	7
BI_05	07/04/2021	500	8
BI_06	01/03/2021	1,400.00	9
BI_08	23/01/2021	600	11
BI_01	01/01/2021	1,500.00	12
BI_03	03/02/2021	800	10
BI_09	13/05/2021	845	7
BI_10	10/06/2021	600	8
BI_11	01/07/2021	2,450.00	6
BI_12	12/08/2021	200	15
BI_13	03/09/2021	150	12

So as you can see savings start at different dates through the year and last x number of months. I have this chart that displays that nicely. 

This is the same as the blue line in below screenshot, just need to replicate the green line to make it work.

 

If I go for another datesytd based on already done calculation I'm getting the same result.

Thank you!

[kasiaw29]

@Greg_Deckler maybe some ideas?