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.
Please use
Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
CALCULATE (
SUM ( MyTable[Amount] ),
ALLEXCEPT ( MyTable, MyTable[Project], MyTable[Type] ),
MyTable[Month] <= CuttentMonth
)
Hi @TIMBOP
please use
Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
CALCULATE (
SUM ( MyTable[Amount] ),
ALLEXCEPT ( MyTable, MyTable[Project] ),
MyTable[Month] <= CuttentMonth
)
Hello @tamerj1
Thank you.
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?
Please use
Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
CALCULATE (
SUM ( MyTable[Amount] ),
ALLEXCEPT ( MyTable, MyTable[Project], MyTable[Type] ),
MyTable[Month] <= CuttentMonth
)
THANK YOU SO MUCH. That works a treat! Much appreciated.
User | Count |
---|---|
145 | |
65 | |
62 | |
59 | |
49 |
User | Count |
---|---|
140 | |
65 | |
63 | |
62 | |
56 |