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] ) )
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 ,
thank you for your reply and thank you for this!
It works, but I need an additional requirement, which invalidates your current solution.
The user should have the ability to use the Month as slicer.
Using the slicer and selecting all the months from January to March, would return this:
| Key | Month | T1 Amount | T2 Amount | Balance |
| K1 | Jan | 10 | 0 | 5 |
| K1 | Feb | 0 | 0 | 0 |
| K1 | Mar | 15 | 5 | 15 |
I've tried to play around with your solution, but I cannot get the desired result.
Can you please help me?
Thanks
- danextian1 year agoSuper User
This is what I am getting...
Did you follow the sample model to the tee or other information in your data being used that is not in your sample?
- danextian1 year agoSuper User
Forgot to attach a sample pbix.
- alessiomissio1 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.