Forum Discussion

alessiomissio's avatar
alessiomissio
Frequent Visitor
1 year ago
Solved

Cumulative Subtraction using Criteria

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 ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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] )
)

     

     

     

     

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

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's avatar
    alessiomissio
    Frequent Visitor
    1 year ago

    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?