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.
Hi,
I'm fairly new to Power BI, so would appreciate some help. I have a budget table (simplified example below) and I'd like a calculation to show my total budget for the year to date.
So in July I'd want the Fundraising YTD budget to show £4000 and the Finance YTD budget to show £1450. I'd like this to automatically update itself each month.
NL | CC | Name | Project | Dept | Apr-16 | May-16 | Jun-16 | Jul-16 | Aug-16 | Sep-16 | etc |
4000 | 1 | Fundraising | CLS | Vol | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | |
4001 | 1 | Finance | CLS | Acc | 850 | 600 | 916 | ||||
4002 | 2 | Sales | MT | Acc | 500 | 500 | 500 | 500 | 500 | 500 | |
4003 | 4 | Telephone | BR | Int | 100 | 100 |
Thank you for your help
Solved! Go to Solution.
Hi @KSt,
From your description, it seems you are using the matrix to display the values, and the “Apr-16”,”May-16”,…. come from one column field, right?
Assume the sample data likes below:
You can create a measure like this:
Cumulative = CALCULATE(SUM(Table1[Value]),FILTER(ALL('Table1'),'Table1'[Date]<=MAX('Table1'[Date])),VALUES(Table1[NL]),VALUES(Table1[CC]),VALUES(Table1[Name]),VALUES(Table1[Project]),VALUES(Table1[Dept]))
Then create a matrix like below:
Best Regards,
Qiuyun Yu
Just in case that your data is in the shape that you've shown, it's pretty easy to transform it into the shape of the solution above: In the query editor, you select the non-months columns, rightclick your mouse and choose: "Unpivot other columns":
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @KSt,
From your description, it seems you are using the matrix to display the values, and the “Apr-16”,”May-16”,…. come from one column field, right?
Assume the sample data likes below:
You can create a measure like this:
Cumulative = CALCULATE(SUM(Table1[Value]),FILTER(ALL('Table1'),'Table1'[Date]<=MAX('Table1'[Date])),VALUES(Table1[NL]),VALUES(Table1[CC]),VALUES(Table1[Name]),VALUES(Table1[Project]),VALUES(Table1[Dept]))
Then create a matrix like below:
Best Regards,
Qiuyun Yu
Just in case that your data is in the shape that you've shown, it's pretty easy to transform it into the shape of the solution above: In the query editor, you select the non-months columns, rightclick your mouse and choose: "Unpivot other columns":
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you ImkeF, unpivoting those columns was what needed to happen 🙂
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 |
---|---|
108 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |