Running Inventory Total DAX

[pxlated]

Hey folks. I have a client with a tricky scenario that I'm stuck on. My transactional tables are filtered via RLS. I am working on creating a matrix visual such that we are looking at a static inventory number and then calculating a rolling inventory for the current month and future months based on having transactional data associated to open orders and monthly sales forecasts. I am having an issue getting this rolling inventory measure to work, and I've been stuck on this for weeks:

# Starting Inventory = CALCULATE(DISTINCTCOUNT('transactional table1'[column1]), 'transactional table'[Source]="Inventory", ALL(DIM_Date))

# Sold = CALCULATE(DISTINCTCOUNT('transactional table1'[column1]), 'transactional table 1'[Source]="Retail")

# Sold (sum total) = CALCULATE([# Sold],ALLSELECTED('transactional table1'))

# ASF = SUM('transactional table 2'[column1])

% modifier = DIVIDE([# sold], [# sold (sum total)], 0)

# Monthly Sales Forecast = ROUND([% modifier]*[# ASF], 0)

# Monthly Inventory Changes = IF([# Sold]>[# Monthly Sales Forecast], [# Sold], [# Sold]+[# Open Orders]-[# Monthly Sales Forecast])

# Rolling Inventory = [# Starting Inventory] + [# Monthly Inventory Changes]

The problem I'm running into is that no matter how I try to create say a running total of [# Monthly Inventory Changes] and then add that to the [# Starting Inventory] such that my [# Rolling Inventory] actually iterates, I'm getting vastly odd numbers for my [# Monthly Inventory Changes - (cumulative] attempts.

Latest attempt at cumulative sum for inventory chages: 

# Monthly Inventory Changes (Sum) =
CALCULATE (
[# Monthly Inventory Changes],
FILTER ( ALL ( DIM_Date[Date] ), DIM_Date[Date] <= MAX ( DIM_Date[Date] ) )
)

I have a feeling the problem that I'm running into is how i'm trying to use a centralized date table for a running total, but that shouldn't be the case since both transactional tables that are referenced in the monthly inventory changes measure should take this into account just fine. any help here would be apprecaited.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.