Forum Discussion
Cumulative Subtraction using Criteria
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] ) )
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] )
)
With some tweaks I've achieved what I was looking for.
Thanks to danextian that guided me to the correct solution.
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
