Cumulative Subtraction using Criteria

[alessiomissio]

Hi all,

I'll try to be clear as much as I can 😀.

I've a tough one and I cannot find any way to do this.
I've 2 tables T1 and T2.
This is T1:

Key	Month	Amount
K1	Jan	15
K1	Feb	0
K1	Mar	10
K1	Apr	0
K1	May	0

and this is T2:

Key	Month	Amount
K1	Jan	0
K1	Feb	0
K1	Mar	5
K1	Apr	5
K1	May	10

What I need to do is to SUBTRACT T2[Amount] from T1[Amount] desregarding the month and ignoring where T2[Amount] is 0, but the amount needs to be cumulated until T1[Amount] is zero.
The final result should be:

Key	Month	Result	Logic
K1	Jan	0	15-(5+5+5)
K1	Feb	0
K1	Mar	5	10-5
K1	Apr	0
K1	May	0

For Jan in T1 (5+5+5) is T2[Amount] of Mar + T2[Amount] of Apr + part of T2[Amount] of May.
For Mar in T1 (5) is the remaining part of T2[Amount] of May.

Of course I've created 2 measure to do subtract the 2 amounts, but by doing this I'm losing the month detail.

Any idea please?

Many thanks

[danextian · Accepted solution]

Hi @alessiomissio 

There isn't that much data to test on but here it is based on what's available. Please note that a proper date column had to be used as cumulative can't be meaningfully applied to text (your month column)

Cumulative Amount t1 = 
CALCULATE (
    SUM ( T1[T1 Amount] ),
    FILTER (
        ALL ( Months ),
        Months[Start of Month] <= MAX ( Months[Start of Month] )
    )
)

Balance = 
SUMX (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( T1, T1[T1 Amount] <> 0 ),
                'Key'[Key],
                Months[Start of Month]
            ),
            "@Cumulative Amount", [Cumulative Amount t1],
            "@T2 Amt", CALCULATE ( SUM ( T2[T2 Amount] ) )
        ),
        "@balance",
            VAR _bal = [@T2 Amt] - [@Cumulative Amount]
            RETURN
                IF ( _bal > 0, 0, _bal )
    ),
    ABS ( [@balance] )
)

[alessiomissio]

With some tweaks I've achieved what I was looking for.

Thanks to @danextian that guided me to the correct solution.

[alessiomissio]

Hi @danextian ,

thank you for your reply and thank you for this!

It works, but I need an additional requirement, which invalidates your current solution.

The user should have the ability to use the Month as slicer.

Using the slicer and selecting all the months from January to March, would return this:

Key	Month	T1 Amount	T2 Amount	Balance
K1	Jan	10	0	5
K1	Feb	0	0	0
K1	Mar	15	5	15

I've tried to play around with your solution, but I cannot get the desired result.

Can you please help me?

Thanks

[danextian]

This is what I am getting...

Did you follow the sample model to the tee or other information in your data being used that is not in your sample?

[danextian]

Forgot to  attach a sample pbix.

[alessiomissio]

Hi @danextian ,

thanks a lot for your reply.

Please find below the link to my PBIX. (sadly I cannot upload files yet).

LINK 

By the way if I try to use the slicer of the Month, reducing data, this is not reflected in the final Balance.

I hope I've explained myself clearly.

Thank you for your time.

[danextian]

[danextian]

I am confused. Are you supposed to show the remaining revenue balance or what's been deducted for every month? Because if what's been deducted, your table is supposed to show 10 and 10 respectively for Jan and Mar, totalling to 20 because T2 is supposed to be deducted regardless of the month in it.

[alessiomissio]

Hi @danextian ,

when you select all the months between January and February, you should obtain:

This because March amount from T2 is subtracted from the first available amount (January) from T1.
I've attached the PBIX above, please rename it from txt to pbix, since Dropbox doesn't allow to download PBIX files.

[danextian]

So it isnt regardless of the month from T2 but from all the months selected in the slicer?

[alessiomissio]

Correct!

I wrote desregarding the month in T2 because I need to subtract the first not zero amount of March of T2 to the first not zero amount of T1.

[Anonymous]

Hi @alessiomissio ,

As danextian mentioned, the balance on 3/1/2025, should be 20 instead of 15 according to your calculation logic. Could you please provide a detailed explanation of your calculation method based on the sample data? This will help us offer the appropriate solution. Thank you.

Best Regards

[Bibiano_Geraldo]

Hi @alessiomissio ,

What determine the part of amount in may?

[Kedar_Pande]

@alessiomissio 

Ensure both tables (T1 and T2) have a relationship on the Key column.

Create Measures

Cumulative Amount T2 =
CALCULATE(
SUM(T2[Amount]),
FILTER(
T2,
T2[Amount] > 0
),
REMOVEFILTERS(T2[Month])
)

Cumulative Balance T1 =
VAR CurrentMonth = MAX(T1[Month])
VAR T1Amount = SUM(T1[Amount])
VAR PreviousBalance =
CALCULATE(
SUM(T1[Amount]) - [Cumulative Amount T2],
FILTER(T1, T1[Month] <= CurrentMonth)
)
RETURN
MAX(0, PreviousBalance)

Final Result =
VAR T1Amount = SUM(T1[Amount])
VAR CumulativeUsedT2 =
SUMX(
FILTER(
T2,
T2[Amount] > 0
),
T2[Amount]
)
RETURN
IF(
T1Amount > 0,
MAX(0, T1Amount - CumulativeUsedT2),
0
)

Add Key, Month, and the Final Result measure to your visualization.
Confirm the results match your expectations.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[alessiomissio]

Hi @Kedar_Pande 

Thanks a lot for your reply!

I confirm that both tables currently have a relation (Many to Many) using Key field.

I've tried the solution but it doesn't work. 

I've understand the measure Cumulative Amount T2.

But I don't understand Cumulative Balance T1. Where is it used?

By the way below is the table with the Final Result measure.

Am I missing something?