MDX conditional sum measure

Question

I have a stock movement table with 4 columns - SKUID, productID, dateIn, dateOut and amount. I wish to be able to track stock levels over time ie on each date in a given range what was the stock level of a given productID - e.g.

(Source Data)

SKUID, productID, dateIn, dateOut, amount
100001, 5, 10/01/2014, 15/01/2014, 500
100002, 5, 10/01/2014, 15/01/2014, 1000
100003, 5, 20/01/2014, 31/12/2049, 500
100004, 5, 20/01/2014, 20/01/2014, 100

(Report Data)

productID, date, amount
5, 10/01/2014, 1500
5, 11/01/2014, 1500
5, 11/01/2014, 1500
...
5, 15/01/2014, 0
5, 15/01/2014, 0
...
5, 20/01/2014, 500
5, 21/01/2014, 500

For each day in the final report I want to see the sum of amount for each product where the dateIn is <= reportDate and dateOut is >= reportDate.

I think this should be done as some kind of conditional sum - ie create a calculated measure as follows :-

sum({null:[dateIn].[Date].CurrentMember}, iif([StockMovement].[dateOut] >= [dateIn].[Date].CurrentMember, [Measures].[Amount], 0))

this doesn't work - it simply gives me the sum of amount for each dateIn

nsousa nsousa · Accepted Answer · 2014-09-16T15:57:49

You shouldn't do this. Even if you can, it's not a good idea.

The table you describe has a grain that can be more or less identified as "shipments", where each shipment is indivisible. Ok, that gives you measures like "number of days per shipment", or "amount going in on date X" or "amount going out on day X".

If you want to track inventory you should use a snapshot fact table, where for each day you add 1 record per product with the amount currently in stock. So, for each day, you take the previous day stock level, add all amounts in, subtract all amounts out and store it on an inventory table.

Check The Data Warehouse Toolkit by Ralph Kimball, look into the Inventory chapter where he describes snapshot fact tables and why we need them. He explains quite well how your approach may have undesirable effects.

MDX conditional sum measure

1 Answers