dax function for stock level line chart

[Glicke]

Hello

I have made an report in Power BI that collects data from our SQL- server with data from AX 2012 that is updated every day.

What I am struggling with now is to make a line chart that shows the current stock level  and the stock level each month in the future, based on current stock level, the delivery of open purchase orders, delivery of open sales order. The calculations would probably look something like this for each month in the line chart:  current month stock level + delivery of purchase orders in month X – delivery of sales orders in month X = Stock level for month X.

The issue that I have is that inventory on hand (todays stock level)  in AX 2012 is posted as multiple transaction based  purchase orders in day X – delivery of sales orders in day X, meaning that you have to summarize all transaction ever made to see today’s stock level and if you just look a specific date you will only see the transactions made that date and not the total stock level.  

I would be very grateful if someone could help me with a DAX functions that could calculate the total stock level each month and reduce and increase it if there is an open sales order or purchase order that month. The relationships between the tables etc. is not an issue its just the measurement/ measurements.

Thanks in advance.   

BR

Christofer     

[v-yulgu-msft]

Hi @Glicke,

To see total stock level each month on a specific day, you could create a measure similar to:

Total stock =
CALCULATE ( SUM ( 'table'[stock] ), ALLEXCEPT ( 'table', 'table'[date].MONTH ) )

For more advice, please post sample data and show us your desired output with an image.

Regards,

Yuliana Gu

[Glicke]

Dear Yuliana Gu

I created a new table with the posted inventory qty:s but without any date columns so I could calculate the total stock level and I have a date table for both purchase and sales orders.

The problem I have now is to calculate the qty for the open purchase orders that haven’t yet been inbounded each month – the total qty on sales orders each month that haven’t yet been outbounded from our warehouse.

I want to be able to monitor the stock level for future month, so I just need to found out how to make the following calculations in order to make a line chart  :

Current stock + purchase qty inbounded – sales qty delivered = cumulated stock month 1

cumulated stock month 1 +  purchase qty inbounded – sales qty delivered = cumulated stock month 2

cumulated stock month 3 +  purchase qty inbounded – sales qty delivered = cumulated stock month 4

etc

BR

Christofer

[Anonymous]

HI, I have similar kind of issue.

How to get weekly planned stock from initial stock+ planned production -  total demand plan

 

BR. J