Hello PowerBI Community.
I've been battling with something and would appreciate some assistance. I have a table with a set of budget and actual spend figures per project per month. These have been brought into PowerBI. I now want to calculate the cumulative / YTD figures for each project.
In the table below I have a simplified example:
Project | Type | Month | Amount || What I want!
Blue | Budget | Jan 22 | $50 || $50
Blue | Budget | Feb 22 | $50 || $100
Blue | Budget | Mar 22 | $50 || $150
Blue | Budget | Apr 22 | $50 || $200
Blue | Actual | Jan 22 | $40 || $40
Blue | Actual | Feb 22 | $65 || $105
Blue | Actual | Mar 22 | $35 || $140
Blue | Actual | Apr 22 | $40 || $180
Red | Budget | Jan 22 | $30 || $30
Red | Budget | Feb 22 | $30 || $60
Red | Budget | Mar 22 | $30 || $90
Red | Budget | Apr 22 | $30 || $120
Red | Actual | Jan 22 | $20 || $20
Red | Actual | Feb 22 | $25 || $45
Red | Actual | Mar 22 | $45 || $90
Red | Actual | Apr 22 | $32 || $122
I can add a calculated column using the following:
Cumulative = CALCULATE(SUM(MyTable[Amount]0, All(MyTable), MyTable[Month] <= EARLIER(MyTable[Month]))
This creates the column, but sums the value for both / all projects.
How do I get the calculation to filter on the project AND type? The last column above shows what I am trying to achieve.
Solved! Go to Solution.
Cumulative = VAR CuttentMonth = MyTable[Month] RETURN CALCULATE ( SUM ( MyTable[Amount] ), ALLEXCEPT ( MyTable, MyTable[Project] ), MyTable[Month] <= CuttentMonth )
I've tried this. I get cumulative values calculated, split by project but not (yet) by type. Does something need to be added to achieve this?
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!