CODEWITHSUNDEEP
powerbidax
Mark Rullo
Mark Rullo

Reputation: 883

DAX to determine quantity of a product in stock for Power BI

In Power BI I'm looking to graph the stock of a product over time. I in essence only have one product, and the table that's stored in has a line for each instance of that item.

An abbreviated example of that table is: ID Volume DateIn DateOut

I'm trying to figure out the best approach of graphing the daily stock of this. I have a date table that's related to DateIn (which I'm unsure of). I've tried creating a measure to determine if the row is in stock, given the filter context.

I'm debating if this needs to be a separate table, used only for stock. So in essence the table would be:

ID Volume InStockOnDate

Does anyone have some guidance? I've been spinning on this on the back-burner for a while now.

Cheers

Upvotes: 0

Views: 826

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I think what you want is a calendar-like table that has a contiguous date column (not related to your existing volume table), to which you add a calculated column.

The calculated column will be your stock and will be computed something like this:

Stock =
CALCULATE(
    SUM(VolumeTable[Volume]),
    FILTER(
        VolumeTable,
        VolumeTable[DateIn] <= StockTable[Date] &&
        VolumeTable[DateOut] > StockTable[Date]
    )
)

Or something along those lines. (I haven't tested this since you supplied no data to work with.)

Upvotes: 1

Related Questions