Month to Month Stock Inventory

[Anonymous]

Hi there!

New to Power BI

I currently have a report that gives the product stock inventory over a certain time period. My data source that holds the product stock inventory was not active until Oct 16th. 

I have a line chart visual showing the percentage of out of stock products on a certain date. I want my visual to adjust depending on the month chosen.

For example, when Oct is selected the graph should begin on the 16th, since that is the beginning of my data source. However, when I have Oct selected, it is showing results from the 1st, saying that 100% of products were out of stock, because again the beginning of my data source is Oct 16th.

 

Then when the month of Nov is selected, it should have all the available dates with values attached to them. The graph should end on the 27th. 

And I can't put filters on these visuals or else I will be missing certain dates in Nov.

Is there a DAX function I can use to apply these changes?

 

[Ashish_Mathur]

Hi,

The easiest way would be to ensure that the Calendar Table starts from Oct 16 and goes till today.  To your visuals/slicers/filters, drag Date from the Calendar Table.

[Anonymous · Accepted solution]

I see! I used CALENDAR instead of CALENDARAUTO and it solve my issue. Thanks!

[amitchandak]

@Anonymous , Do you stock snapshot or do you rebuild inventory using purchase and sales

In case snapshot, You can use lastnonblankvalue

calculate(lastnonblankvalue (date[Date], Sum(Table[Inv Value]) , Filter(all(Date), Date[Date] <= max(Date[Date]) ) )

Or you can rebuild using

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) +
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw