topic Re: How to calculate Cumulative monthly saving in year from start date of Project in DAX Commands and Tips

How to calculate Cumulative monthly saving in year from start date of Project

kayo — Sun, 27 Feb 2022 10:24:31 GMT

Hi I would like to achieve calculating monthly saving of the year

I have a data with Montly saving starting from project implementation date

Example, May saving 50 euro, then June saving of 30 Euro, then Cumulative saving if June is 50+50+30 = 130

When project implemented in August with saving of 10 euro, then August cumulative saving is 50+50+50+50+30+30+30+10 = 300

Can someone please help

Thanks

Re: How to calculate Cumulative monthly saving in year from start date of Project

littlemojopuppy — Sun, 27 Feb 2022 16:24:01 GMT

Hi kayo

Try this

CALCULATE( SUM([MonthlySaving]), DATESYTD(DateTable[DateField]) )

DATESYTD is a time intelligence function so make sure you have a date table and that it is marked appropriately.

Hope this helps!

Re: How to calculate Cumulative monthly saving in year from start date of Project

kayo — Mon, 28 Feb 2022 09:14:57 GMT

Thank you Littlemojopuppy for your reply

Your solution igives the same result as TOTALYTD(Sum(Monthly saving), Datetable(Datefield) and returns 210 as cumulative sum,

What I am looking for is to repeat saving after it is implemented

In my example matrix, accumulated sum I want to achieve is 840

Is there way to do?

In other word, I want to cumulate value (A cumulation of monthly saving) again to have my result (B)

Re: How to calculate Cumulative monthly saving in year from start date of Project

littlemojopuppy — Mon, 28 Feb 2022 18:42:56 GMT

Hi kayo apologies...I missed that when I read the initial question.

I mocked up some data and came up with this...

Hope this helps!

Re: How to calculate Cumulative monthly saving in year from start date of Project

kayo — Tue, 01 Mar 2022 07:44:17 GMT

This is great !

Thank you so much for your help

Re: How to calculate Cumulative monthly saving in year from start date of Project

littlemojopuppy — Tue, 01 Mar 2022 14:13:40 GMT

kayo you're welcome! Glad I could help!

Re: How to calculate Cumulative monthly saving in year from start date of Project

littlemojopuppy — Tue, 01 Mar 2022 14:15:46 GMT

DAX for measures for anyone who finds this later...

Total Savings = SUM(RawData[Savings]) YTD Savings = TOTALYTD( [Total Savings], 'Calendar'[Date] ) Cumulative YTD Savings = VAR MonthEnding = CALCULATETABLE( VALUES('Calendar'[MonthEndingDate]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ), DATESYTD('Calendar'[Date]) ) VAR CumulativeSavings = CALCULATETABLE( ADDCOLUMNS( MonthEnding, "YTDSavings", [YTD Savings] ), REMOVEFILTERS('Calendar') ) RETURN SUMX( CumulativeSavings, [YTDSavings] )