Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I use power query to import the data from database with current year beginning balance( C.BEG.BAL) and current month1 (C.M1), which is January, current month 2 (C.M2), which is Febuary and etc. and also previou year beginning balance (P.BEG.BAL) and previous month current balance (P.M1). I wonder if i can summerize current YTD balance and previous YTD by given a random month. Thank you!
Solved! Go to Solution.
Hi @yansong,
Based on my test, you coud refer to below steps:
1.Copy your row table. Remove the previous month value in row table and remove the currently month value in copied table.
2.Transpose the tables and add index.
3.Merge the two tables and delete one index columns. Rename the other index column as "Month". Apply it.
4. Create a measue and you could get the result.
Measure = CALCULATE(SUM('Merge1'[Value]),FILTER(ALL('Merge1'),'Merge1'[Month]<=MAX('Merge1'[Month])))+CALCULATE(SUM('Merge1'[Table1.Value]),FILTER(ALL('Merge1'),'Merge1'[Month]<=MAX('Merge1'[Month])))
You can also download the PBIX file to have a view.
Regards,
Daniel He
Hi @yansong,
From your description, I could not understand the sentence "summerize current YTD balance and previous YTD by given a random month" post in your problem, could you please post your desires result if possible?
Regards,
Daniel He
Sure. If I select February for exmaple, it will summarize C.BEG.BAL+C.M1+C.M2 (Current YTD) and also P.BEG.BAL+P.M1+P.M2( Previous YTD). Let me know if you are still confused. Thank you for your help.
Hi @yansong,
Based on my test, you coud refer to below steps:
1.Copy your row table. Remove the previous month value in row table and remove the currently month value in copied table.
2.Transpose the tables and add index.
3.Merge the two tables and delete one index columns. Rename the other index column as "Month". Apply it.
4. Create a measue and you could get the result.
Measure = CALCULATE(SUM('Merge1'[Value]),FILTER(ALL('Merge1'),'Merge1'[Month]<=MAX('Merge1'[Month])))+CALCULATE(SUM('Merge1'[Table1.Value]),FILTER(ALL('Merge1'),'Merge1'[Month]<=MAX('Merge1'[Month])))
You can also download the PBIX file to have a view.
Regards,
Daniel He
Thank you, Daniel! I think that works!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |