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] ) )
I am confused. Are you supposed to show the remaining revenue balance or what's been deducted for every month? Because if what's been deducted, your table is supposed to show 10 and 10 respectively for Jan and Mar, totalling to 20 because T2 is supposed to be deducted regardless of the month in it.
Hi danextian ,
when you select all the months between January and February, you should obtain:
This because March amount from T2 is subtracted from the first available amount (January) from T1.
I've attached the PBIX above, please rename it from txt to pbix, since Dropbox doesn't allow to download PBIX files.
- danextian1 year agoSuper User
So it isnt regardless of the month from T2 but from all the months selected in the slicer?
- alessiomissio1 year agoFrequent Visitor
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