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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.