PowerBI - calculate cumulative values with multiple criteria

[TIMBOP]

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:

• the Projects are: Blue, Red
• the Types are: Budget, Actual
• the Months are: Jan, Feb, Mar and April 2022
• the table name is [MyTable]

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.

[tamerj1]

Hi @TIMBOP 

please use

Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
    CALCULATE (
        SUM ( MyTable[Amount] ),
        ALLEXCEPT ( MyTable, MyTable[Project] ),
        MyTable[Month] <= CuttentMonth
    )

[TIMBOP]

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?

[tamerj1 · Accepted solution]

@TIMBOP 

Please use

Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
    CALCULATE (
        SUM ( MyTable[Amount] ),
        ALLEXCEPT ( MyTable, MyTable[Project], MyTable[Type] ),
        MyTable[Month] <= CuttentMonth
    )

[TIMBOP]

THANK YOU SO MUCH.  That works a treat! Much appreciated.