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] ) )
Forgot to attach a sample pbix.
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.
- danextian1 year agoSuper User
- danextian1 year agoSuper User
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?