Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Vivenna
Regular Visitor

Snapshot inventory values and stock analysis

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:

Vivenna_0-1666767594488.png

 

 

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 🙂

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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.

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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.

@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 🙂

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.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.