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 ...
  • danextian's avatar
    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] )
    )