topic Re: Need to propagate a monthly total for each month in DAX Commands and Tips

Need to propagate a monthly total for each month

CMSGuy — Sat, 22 Jun 2024 15:46:50 GMT

This is more of a data shaping question. I have a dataset that has a starting date (I am using the month from that date) and has a monthly savings amount. This data is coming from a SharePoint list and the list has a calculated column for that monthly amount. I have a matrix that has the name of the project with it's projected yearly (2024) savings, the start date of the savings, and the monthly savings (The total anticipated savings / (13-MonthNumberFromStartDate). How can the monthly savings total be shown for each month after the starting date and include the starting month? In the "DevOps Consolidation" example, the estimated 2024 yearly savings is $50,000 and this correctly is showing $7,142.86 starting in June (as 50K/7 is that amount).

I must be just missing something. Thanks for the help.

Re: Need to propagate a monthly total for each month

some_bih — Mon, 24 Jun 2024 06:29:58 GMT

Hi CMSGuy basically you need year to day (YTD) view on your data?

If yes, and if your model for Date table is connected with Start date which you refere then simple YTD formula should work fine, like below, adjust your measure and your date table

CALCULATE (
[Your measure],
DATESYTD ( 'Date'[Date] )
)

Link for YTD function

Re: Need to propagate a monthly total for each month

Anonymous — Mon, 24 Jun 2024 07:41:58 GMT

Thanks for the reply from some_bih , please allow me to provide another insight:

Hi CMSGuy ,

Maybe you can try formula like below:

Total Monthly Savings = VAR SelectedMonth = MAX ( 'Date'[MonthNumber] ) VAR SelectedYear = MAX ( 'Date'[Year] ) RETURN SUMX ( FILTER ( 'Projects', YEAR ( 'Projects'[Start Date] ) = SelectedYear && MONTH ( 'Projects'[Start Date] ) <= SelectedMonth ), 'Projects'[Monthly Savings] )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need to propagate a monthly total for each month

CMSGuy — Mon, 24 Jun 2024 10:40:42 GMT

Thanks for the reply. I do have that part working with not problem, but how do I take that monthly savings amount and show it on each month beginning with the starting month and continuing each month thereafter? This is a sample of an excel sheet. The first item has total savings of 63,600 and begins in January. Each month is 5,300 savings. The second is a total of 51,500 and starts in February, with monthly savings of 4681.82; then the third example is a total of 66,000 starting in March (so ten months @ 6,600).

I am getting the proper monthly savings, but I would like to show that value in the starting month and then each month after to the end of the calendar year

Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	TOTAL
5300	5300	5300	5300	5300	5300	5300	5300	5300	5300	5300	5300	63600
	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	51500
		6600	6600	6600	6600	6600	6600	6600	6600	6600	6600	66000

Re: Need to propagate a monthly total for each month

CMSGuy — Mon, 24 Jun 2024 10:52:28 GMT

Thanks for the reply. This is a sample of an excel sheet. The first item has total savings of 63,600 and begins in January. Each month is 5,300 savings. The second is a total of 51,500 and starts in February, with monthly savings of 4681.82; then the third example is a total of 66,000 starting in March (so ten months @ 6,600).

I am getting the proper monthly savings, but I would like to show that value in the starting month and then each month after to the end of the calendar year. Would your formula do that?

Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	TOTAL
5300	5300	5300	5300	5300	5300	5300	5300	5300	5300	5300	5300	63600
	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	4681.818	51500
		6600	6600	6600	6600	6600	6600	6600	6600	6600	6600	66000