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:

KeyMonthAmount
K1Jan15
K1Feb0
K1Mar10
K1Apr0
K1May0

and this is T2:

KeyMonthAmount
K1Jan0
K1Feb0
K1Mar5
K1Apr5
K1May10

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:

KeyMonthResultLogic
K1Jan015-(5+5+5)
K1Feb0 
K1Mar510-5
K1Apr0 
K1May0 

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

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

     

     

     

     

15 Replies

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

     

     

     

     

    • alessiomissio's avatar
      alessiomissio
      Frequent Visitor
      1 year ago

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

      Thanks to danextian that guided me to the correct solution.

    • alessiomissio's avatar
      alessiomissio
      Frequent Visitor
      1 year ago

      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:

      KeyMonthT1 AmountT2 AmountBalance
      K1Jan1005
      K1Feb000
      K1Mar15515


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

       

      Can you please help me?

       

      Thanks

      • danextian's avatar
        danextian
        Super User
        1 year ago

        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?

  • 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?