Filtering on running total values

[sprakash1192]

I have a running total measure that calculates roll forward of my inventory -

RollForward = CALCULATE(

[Inv_Qty_Cont_Agg],

FILTER(

ALLSELECTED('Calendar'[CALDATE_0]),

Calendar'[CALDATE_0]<= MAX(Calendar'[CALDATE_0])

))

I take this measure and put it on a matrix visual with dates across the top. I then want to be able to filter my visual to only where my RollForward value is less than zero. So, I want to see dates where the roll forward number goes below zero. When I put a filter on the visual to RollForward less than 0, the visual only filter where the total at the end is less than zero. If there were negaitves on some days, but the total at the end is positive, the visual wont display those instances when the less than zero filter is applied.

How can I resolve this?

[Anonymous · Accepted solution]

Hi @sprakash1192 ,

I have built a data sample to test:

You could use the following formula to filter RollForward <0

RollForward = var _v= CALCULATE([Inv_Qty_Cont_Agg],FILTER(ALLSELECTED('Calendar'[CALDATE_0]),'Calendar'[CALDATE_0]<=MAX('Calendar'[CALDATE_0])))

return IF(_v>0,BLANK(),_v)

Measure = IF(HASONEVALUE('Calendar'[CALDATE_0]),[RollForward],SUMX('Calendar',[RollForward]))

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