Determine artcles that are on stock on certain date?

[markblom]

Hi,

In power bi I have a table ArticleStockRecords with 3 columns:
ArticleNumber,
InDate and
OutDate.

An article is on stock when InDate is not null and <= selected Date and OutDate is null or > selected date.

Furthermore, I have a Date tabel with FullDateAlternateKey as column. I have a slicer added to select a single date. Currently, I have a relationship defined between Date[FullDate] and ArticleStocks[InDate].

How can I write a measure or column to list the ArticleStock records that were on stock on the date that is selected in the Date slicer?

I created the following measure:

Stock on Date = 
CALCULATE(
    COUNT(ArticleStockRecords[articlenumber]),
    FILTER(
        ArticleStockRecords,
        ArticleStockRecords[indate] <= MAX('Date'[FullDateAlternateKey].[Date]) &&
        (ArticleStockRecords[outdate] > MAX('Date'[FullDateAlternateKey]) || ISBLANK(ArticleStockRecords[outdate]))
    )
)

When I select April 14th 2023 in the date slicer I only get ArticleStockRecords with the same InDate so I am assuming that the relationship between the two tables is not OK. But when I de-activate the relationship I can not filter ArticleStockRecords anymore.

Anyone a suggestion what I should change?

[johnt75 · Accepted solution]

I think you can delete the relationship and use the below measure

Stock on date =
VAR SelectedDate =
    MIN ( 'Date'[Date] )
VAR NumInStock =
    CALCULATE (
        COUNTROWS ( 'ArticleStockRecords' ),
        'ArticleStockRecords'[in date] <= SelectedDate
            && (
                ISBLANK ( 'ArticleStockRecords'[out date] )
                    || 'ArticleStockRecords'[out date] > SelectedDate
            )
    )
RETURN
    NumInStock

[markblom · Accepted solution]

Thanks for your help! This seems to work to calculate a quantity of articles on stock!

What if I would like to create a table of all ArticleStockRecords that were on stock for that given date? Can that be done without a relationship between Date and ArticleStockRecords?

[johnt75 · Accepted solution]

I think you can delete the relationship and use the below measure

Stock on date =
VAR SelectedDate =
    MIN ( 'Date'[Date] )
VAR NumInStock =
    CALCULATE (
        COUNTROWS ( 'ArticleStockRecords' ),
        'ArticleStockRecords'[in date] <= SelectedDate
            && (
                ISBLANK ( 'ArticleStockRecords'[out date] )
                    || 'ArticleStockRecords'[out date] > SelectedDate
            )
    )
RETURN
    NumInStock

[markblom · Accepted solution]

Thanks for your help! This seems to work to calculate a quantity of articles on stock!

What if I would like to create a table of all ArticleStockRecords that were on stock for that given date? Can that be done without a relationship between Date and ArticleStockRecords?

[johnt75 · Accepted solution]

Yes, add the columns from your date table and the columns from the article stock table to the visual and add the stock on date measure as a visual level filter to only show where the value is greater than 0