How to filter a Matrix Visual based on a Cumulative Sum

[Anonymous]

Hi all, I have a table of data like this called 'Stock':

ID	Item	Date	Site	Type	Quantity
Inventory	1003706	09/14/20	WARM1	Inventory	14,636
3010209974	1003706	09/14/20	WARM1	Sales Order	-10,000
100017032	1003706	09/23/20	WARM1	Production Orders	49,000
3010210118	1003706	09/14/20	WARM1	Sales Order	1,000
3010210277	1003706	09/15/20	WARM1	Sales Order	1,500
3010210355	1003706	09/15/20	WARM1	Sales Order	12,000

And a measure to calculate the cumulative sum of this data:

Quantity running total in Date =

CALCULATE(

    SUM(Stock[Quantity]),

    FILTER(

        ALLSELECTED(Stock[Date]),

        ISONORAFTER(Stock[Date], MAX(Stock[Date]), DESC)

    )

)
)

Which is loaded into a matrix table like this:

[Anonymous · Accepted solution]

CONTINUED

loaded into a table like this:

I'd like to filter this matrix to only show items where the cumulative sum TOTAL is <0.  I've tried filtering by where the Cumulative Sum measure is <0, but this filters the table to only calculate on transactions where the quantity is <0.  Does anyone know how to do this?

[amitchandak]

@Anonymous ,

Try like

Quantity running total in Date =
var _cumm =
CALCULATE(
SUM(Stock[Quantity]),
FILTER(
ALLSELECTED(Stock[Date]),
ISONORAFTER(Stock[Date], MAX(Stock[Date]), DESC)
)
)
)

return if(_cumm <=0 , then _cumm else blank())

If it works if may have some impact on the grand total so switch formula based on isfiltered or hasonevalue

[Anonymous]

Hi @amitchandak, thank you for the reply, but this formula doesn't seem to get the result I'm looking for.  Using this formula, now the table only shows the days with a negative cumulative value and the Total has disappeared from the visual:

Do you have any other ideas on how to achieve this?

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

There should be no problem with the method, but the order of the conditions is reversed.

Quantity running total in Date =
var _cumm =
CALCULATE(
SUM(Stock[Quantity]),
FILTER(
ALLSELECTED(Stock[Date]),
ISONORAFTER(Stock[Date], MAX(Stock[Date]), DESC)
)
)
)

return IF(_cumm<0,BLANK(),_cumm))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

CONTINUED

loaded into a table like this:

I'd like to filter this matrix to only show items where the cumulative sum TOTAL is <0.  I've tried filtering by where the Cumulative Sum measure is <0, but this filters the table to only calculate on transactions where the quantity is <0.  Does anyone know how to do this?