Skip to main content
cancel
Showing results for 
Search instead 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

Reply
CMSGuy
Helper III
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.

 

CMSGuy_0-1719070975935.png

 

4 REPLIES 4
v-kongfanf-msft
Community Support
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]
    )

vkongfanfmsft_0-1719214822834.png

 

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.

 

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

 

JanFebMarchAprilMayJuneJulyAugSeptOctNovDec TOTAL
53005300530053005300530053005300530053005300530063600
 4681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.81851500
  660066006600660066006600660066006600660066000
some_bih
Super User
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 (
[Your measure],
DATESYTD ( 'Date'[Date] )
)

Link for YTD function





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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?

 

JanFebMarchAprilMayJuneJulyAugSeptOctNovDec TOTAL
53005300530053005300530053005300530053005300530063600
 4681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.8184681.81851500
  660066006600660066006600660066006600660066000

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors