Issue with rolling up rolling sum calculations to a hierachy

Power BI
1

I am doing a rolling sum om some data, the rolling sum seems to work but when i roll up the rolling to another hierarchy, the negatives in the calculation still reduces the value.
I have two basic dax calculations:
Sum = ‘Inventory Balances Components -Report R5841003’[Quantity On Hand (Dollars)] + ‘V3411L - MRP Detailed Message Review’ [Quantity Available (Dollars)] + ‘V4311CC - Open Orders’[Open Orders (Dollars)] - ‘V3412A- MRP Pegging Inquiry’[Quantity Ordered (Dollars)]

Rolling Sum Qty W/O -ve inventory =
VAR RollingSum = CALCULATE(
[Sum of Quantities],
FILTER(
ALLSELECTED(DateTable),
DateTable[Date] <= MAX(DateTable[Date])
)
)
RETURN
Rolling sum

rolling sum
rolling sum1282×566 23 KB

I added a condition to the rolling sum to take care of the negatives, at the granular level it works but when I roll up to another hierarchy, it still applied the negative calculation

image
image1281×573 20 KB

thanks for your help

2

Hi @wilford.mitchell ,

could you provide PBIX file also?

Thank you.

4

FInd attached

5

Hi @wilford.mitchell ,

Apologize for little delay,

It seems that in firsta and second table (different one ) you do not have same filters applied. So could you check that?

Also could you check relationships between ItemBranch table with all other tables involved in following calculation:

Sum of Quantities = SUM('Inventory Balances Components'[Quantity On Hand]) + SUM('MRP Detailed Message Review'[Quantity_Available]) + SUM('Open Orders'[Open Orders]) - SUM('MRP Pegging Inquiry'[Quantity Ordered])

Thank you.