Show Running Inventory-Roll Up previous Data but Include in Calculation for Matrix

[ashako88]

Hi All,

Need some help showing inventory. The requirement is:

a) to show blue curly data (90 days in the past) needs to roll up into 1 row that needs to be called 'Past Due' row right above 11/21. (first snapshot)

b) Then a filter(?) needs to be added to show 12 weeks out from today. (second snapshot, this image neglects data prior to 11/21)

When filter added for 84 days out (12 weeks), it neglects the incoming material and ship data (Ship-DDOO) which gives the wrong output in Ending Inventory column and Running Safety Stock.

Any clue how I can accomplish this a & b?

Calculataion Note: 

Ending Inventory = calculate(SUM(ORIS[Stock-Inv])+SUM(ORIS[ShipNt])+SUM(ORIS[PchOrd])-ORIS[Ship-DDOO(vs NF)],FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date]<=max('Date Table'[Date])))

Running Safety Stock = Calculate(DIVIDE([Ending Inventory], SUM(ORIS[SafeSt])), FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <=MAX('Date Table'[Date])))

12 week filter 

Thanks!

[v-chenwuz-msft · Accepted solution]

Hi @ashako88 ,

1. To add a row. unfortunately, it is impossible to add rows in Martix table unless you add one row in the underlying data. But this will change "week start" column type to text and something will go wrong. Maybe a row where Material = P50628 and Week Start = blank() will work. And the measure:
   • IF( selectedvalue('table'[Week Start]=BLANK()), calculation for  Past Due, calculation for normal ).
2. Next 84 days. create a measure:
   • next 84 days = IF('Date'[Date]>=today() && 'Date'[Date]<=today()+84,1,0)
   • put this measure in Filters pane and set items is 1

Maybe you can share some example data without sensitive data.

Best Regards

Community Support Team _ chenwu zhu

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