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] ) )
alessiomissio
1 year agoFrequent Visitor
Hi danextian ,
thanks a lot for your reply.
Please find below the link to my PBIX. (sadly I cannot upload files yet).
By the way if I try to use the slicer of the Month, reducing data, this is not reflected in the final Balance.
I hope I've explained myself clearly.
Thank you for your time.
danextian
1 year agoSuper User