Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
naoyixue1
Post Patron
Post Patron

Calculate monthly YTG running total AND always actualize monthly YTD value in the same column

Hey there,

 

I'm working on one report which requests to calculate the monthly YTG running total based on the actualized YTD value as below. That YTG value will keep update since we will actualize YTD data month by month. (see below fomula) 

 

C = last month leftover (B-A) + this month B - this month A

 

Here I marked the actual value in orange and calculated value in yellow on both Jun table and Jul table. 

 

By the end of Jun, Jul C is calculated.  Jul C = Actualized Jun C + Jul B - Jul A = 7+12-13 = 6

By the end of Jul, Jul C is actualized as 8.

same as the following months value.

 

 (A, B and C are from the different tables, so I can apply summarize dax to combine A, B and actualized C into one table). SO, how should i calculate in BI? In order to make sure that YTD C value is always actualized month by month and YTG is calculated based on the latest month's actual value. Thanks! 

 

naoyixue1_3-1669845010229.png

 

 

 

 

3 REPLIES 3
naoyixue1
Post Patron
Post Patron

Any thoughts? Thanks!

naoyixue1
Post Patron
Post Patron

@v-luwang-msft My expected results is the table same as above. (but it can be calculated each month with the new month actualized number) For example, when I actualize Jun C value, then Jul thru Dec C value will be calculated automatically. After we move to Aug (at that time, Jul C value will be actualized, then Aug thru Dec Value C will re-calculated based on the Jul actualized data). 

v-luwang-msft
Community Support
Community Support

Hi @naoyixue1 ,

What you expect result output according the two table?

 

 

Best Regards

Lucien

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.