cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Need to propagate a monthly total for each month

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.

4 REPLIES 4
Community Support

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,

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

Helper III

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
Super User

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 (
DATESYTD ( 'Date'[Date] )
)

Proud to be a Super User!

Helper III

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