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] ) )
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.
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.
- alessiomissio1 year agoFrequent Visitor
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.