Snapshot inventory values and stock analysis

[Vivenna]

Hi all

I want to analyse my inventory which is like a snapshot fact table. Each line has product key, date, cost and stock.
The stock amount is logged every day for each item over several years and the cost changes on some days.

This is how my table looks. It's joined to Date and Product tables. As said earlier there is an entry for every day for years:

What I want is to be able to get my inventory value for different date points, such as year or month, etc, in a graph or table. I would like to show my stock amount over time for different products too.

I have tried with dax to do this but the resulting numbers are too big. For example, I tried = SUMX(InventoryTable, InventoryTable[Cost$] * InventoryTable[Stock]) but this gives me huge numbers so it is obviously summing everything rather than treating each row result as it's own for each product and date.

Very confused by this, please advise thank you 🙂

[johnt75 · Accepted solution]

If you know that you have an entry for every product on the last day of every month then you could create a measure like

Inventory Snapshot =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        SUMX ( InventoryTable, InventoryTable[Cost$] * InventoryTable[Stock] ),
        'Date'[Date] = ReferenceDate
    )
RETURN
    Result

If you might have some dates or products missing then it would be a bit more complicated as you would need to work out the last date within the period for which you did have data for the current product.

[Vivenna]

@johnt75  I hope it's ok to ask you one further question building on this!

If I wanted to create the variable ReferenceDate for both the first and last date in a month but let's say my entire dataset started, for example 25-11-2009 and ended 10-06-2018, how would I adjust the dax to still calculate? I couldn't use MIN(DATE[DATE]) for the start because it's missing the dates prior to 25-11-2009, is that correct?

Thanks 🙂

[johnt75]

Ideally the Date table should consist of full years, regardless of whether you have data for all the dates. It should run from Jan 1st in the first year you have data for until Dec 31st in the last year you have data for.

The idea behind using MAX('Date'[Date]) as the reference date is that you can put other columns from your date table onto the chart or table visual, e.g. Year & Month, and the MAX('Date'[Date]) will then pick up the last date in the month in the current context and use that to show the values as at the end of the month.

You could duplicate the code replacing MAX with MIN to show the values as at the start of the given month, but I'm not sure how beneficial it would be to have both. The values at the start of the month are likely to be very similar to the values at the end of the previous month so for consistency it may be better to stick to one or the other.

[Vivenna]

Thank you for your reply! Your solution worked perfectly for my dataset which had no missing dates or products 🙂