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

Inventory Level Projections

Hey Everyone,

 

I have the following data and I want to create an inventory projection:

 

DateInv Changes

10/30/20171000
11/13/20171000
12/4/20171041
1/4/20181000
1/18/20181000

 

I have a 5 Week Average measure that spits out a daily shipment amount. I want to multiply that by the number of shipping days left between today and each subsequent date, which I have a measure to do already.

 

Then I'd like to plot the current actual shipments vs. projected, on a Line and Clustered Column chart, with the projected inventory level as the line chart, changing dynamically as the 5wk trend changes. 

 

The problem I'm running into is when I use the 5 Week Average measure, it doesn't allow it to be a static value that I can use on a future date table. Hopefully that all makes sense, let me know if you need any clarification.

 

Here are a few of the measures I'm using, I'm sure it's got to be in the way I'm locking in some of the measures:

 

 

DistinctDatesCount = SUM(DateKey[IsWorkdayNoHoliday]) 

 

Units Shipped >10/30 = 
CALCULATE(
	[Units Shipped],
		DATESBETWEEN(
			DateKey[Date].[Date],"10/30/2017",NOW())
)
Current Inventory = 
CALCULATE(
	[Inventory],
		DATESBETWEEN(
			DateKey[Date].[Date],"10/30/2017",NOW())
) - [Units Shipped >10/30]
Units ShipPD Current 5wk Trend = 
VAR RQED = [RQEndDate]
RETURN
DIVIDE(
    CALCULATE(
        [Units Shipped],
        FILTER(DateKey,DateKey[Date]>RQED-35 && DateKey[Date]<=RQED)
    ),
    [DistinctDatesCount5wk]
)

 

2 REPLIES 2
Highlighted
libratic Frequent Visitor
Frequent Visitor

Re: Inventory Level Projections

 
Moderator v-qiuyu-msft
Moderator

Re: Inventory Level Projections

Hi @libratic,

 

Would you please share the complete sample data of used tables? And clarify desired results according to these sample data? 

 

It would be better if you could share pbix file with same data for us. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.