11-01-2017 12:34 PM
I have the following data and I want to create an inventory projection:
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] )
11-03-2017 06:44 PM
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.