March 15, 2016 at 11:19 am
Greetings MDXperts!
I'm trying to forecast when we'll run out of inventory.
I have an Analysis Services cube that has the following measures at the date (single day) level:
[Measures].[Forecast Qty Sold] (going out 24 months into the future)
[Measures].[End of period Qty] (this value for the current date is the current quantity on hand)
I also have a Date Status attribute in the Date dimension. Dates in the past =1, today = 2, dates in the future = 0. It's updated nightly when the cube is reprocessed.
So now I want to create a measure that has a single date value for each item number representing the day we'll run out of the current stock, i.e. the last date on which the quantity on hand exceeds the cumulative sum of forecast unit sales from current date to whatever date we run out.
Here's how I solved the problem in SQL:
With ForecastAvail
--Use windowing funciton to predict daily available qty based on current available qty and forecast
as (
Select a.ItemID
, ForecastDate
, RemainQtyAvail = isnull( b.QtyInStock, 0)
-SUM(a.Forecast) over
(Partition by ItemID
order by ForecastDate
)
from Forecast a
left outer join Inventory..Inventory (nolock) b
on a.ItemID = b.ItemID
)
--Select last date where qty available exceeds cumulative forecast for each item
--Use that date to calculate days and weeks of supply
select ItemID
, LastAvailDate = max( Case when RemainQtyAvail > 0
then ForecastDate else 0 end)
, DaysOfSupply = datediff(dd, getdate(), max( Case when RemainQtyAvail > 0
then ForecastDate else 0 end))
, WeeksOfSupply = datediff(week, getdate(), max( Case when RemainQtyAvail > 0
then ForecastDate else 0 end))-1
from ForecastAvail
group by ItemID
That yields results:
[font="Courier New"]
itemnoLastAvailDaysOfSupplyWeeksOfSupply
102REDSOCK4/13/2016 0:00293
102BLUSOCK4/11/2016 0:00273
102YELSOCK4/25/2016 0:00415[/font]
Unfortunately, with my meager MDX skills, I don't know how to approach this in an Analysis Services context. Any suggestions? Thanks!
March 15, 2016 at 4:26 pm
A partial solution, for those of you playing at home.
I still need to add the cumulative forecast and inventory measures, and calculate the out of stock dates.
With
Member [Measures].[Last Full Week Start] as
Exists(
[Date Register].[Planning Week Hierarchy].[Planning Week Start].Members,
{ [Date Register].[Date Status].&[2]}
).Item(0).Lag(1).MemberValue
Member [Measures].[Last Full Week Units] as
sum( Exists(
[Date Register].[Planning Week Hierarchy].[Planning Week Start].Members,
{ [Date Register].[Date Status].&[2]}
).Item(0).Lag(1)
, [Measures].[Item Qty Gross]
)
Member [Measures].[Last Week Last Year Units] as
sum( Exists(
[Date Register].[Planning Week Hierarchy].[Planning Week Start].Members,
{ [Date Register].[Date Status].&[2]}
).Item(0).Lag(35)
, [Measures].[Item Qty Gross]
)
Member [Measures].[Slope] as
[Measures].[Last Full Week Units]/[Measures].[Last Week Last Year Units]
Member [Measures].[Forecast] as
[Measures].[Slope]
* SUM([Date Register].[Planning Week Hierarchy].CurrentMember.Lag(52)
, [Measures].[Item Qty Gross])
SELECT
--[Date Register].[Date].AllMembers
{ [Date Register].[Planning Week Hierarchy].[Planning Week Start].AllMembers
}
ON ROWS,
{[Planning Hierarchy].[Planning Prod Hierarchy].[Hier Product Group Code].ALLMEMBERS
*
{[Measures].[Item Qty Gross]
, [Measures].[Last Full Week Start]
, [Measures].[Last Full Week Units]
, [Measures].[Last Week Last Year Units]
, [Measures].[Slope]
, [Measures].[Forecast]}
}
ON COLUMNS
FROM
(
SELECT
{
[Date Register].[Calendar Year Start].&[2014-01-01T00:00:00]
:[Date Register].[Calendar Year Start].&[2018-01-01T00:00:00]
} ON COLUMNS
FROM
(
SELECT
{
[Planning Hierarchy].[Product Line].&[100]
,[Planning Hierarchy].[Product Line].&[101]
,[Planning Hierarchy].[Product Line].&[106]
,[Planning Hierarchy].[Product Line].&[105]
} ON COLUMNS
FROM
(
SELECT
{[Date - Sales Register].[Date Status].&[1]} ON COLUMNS
FROM [PerformanceManagement]
)
)
)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply