Forum Discussion
Cumulative Subtraction using Criteria
- 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] ) )
So it isnt regardless of the month from T2 but from all the months selected in the slicer?
Correct!
I wrote desregarding the month in T2 because I need to subtract the first not zero amount of March of T2 to the first not zero amount of T1.
- Anonymous1 year agoNot applicable
Hi alessiomissio ,
As danextian mentioned, the balance on 3/1/2025, should be 20 instead of 15 according to your calculation logic. Could you please provide a detailed explanation of your calculation method based on the sample data? This will help us offer the appropriate solution. Thank you.
Best Regards