Forum Discussion
alessiomissio
1 year agoFrequent Visitor
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 ...
- 1 year ago
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] ) )
danextian
1 year agoSuper User
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
1 year agoFrequent Visitor
With some tweaks I've achieved what I was looking for.
Thanks to danextian that guided me to the correct solution.