cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashako88
Frequent Visitor

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

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])))
 

ashako88_0-1637777288883.png

 

12 week filter 

ashako88_1-1637777756629.png

 

Thanks!

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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.

 

View solution in original post

1 REPLY 1
v-chenwuz-msft
Community Support
Community Support

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.

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors