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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors