out of stock by date

[sekka_1985]

Dear all

please help me with Power BI:

I have two Data Table:

Table: Stock

Table: Demand

I have add column whitch give me the runing total by day. whitch is run

ning good. see below:

Material	Calendar Day	demand	runningTotal_
100000	28.04.2023 00:00	170	170
100000	15.05.2023 00:00	255	425
100000	23.05.2023 00:00	255	680
100000	15.06.2023 00:00	85	765
100000	22.06.2023 00:00	85	850
100000	29.06.2023 00:00	425	1275
100000	19.07.2023 00:00	340	1615
100000	20.07.2023 00:00	255	1870
100000	03.08.2023 00:00	170	2040
100000	17.08.2023 00:00	255	2295
100000	24.08.2023 00:00	170	2465
100000	28.08.2023 00:00	85	2550
100000	07.09.2023 00:00	255	2805
100000	20.09.2023 00:00	170	2975
100000	05.10.2023 00:00	255	3230
100000	11.10.2023 00:00	170	3400
100000	25.10.2023 00:00	255	3655
100000	09.11.2023 00:00	170	3825
100000	20.11.2023 00:00	170	3995

no i like to combine this both information. to a dashboard like this.

Stock is: 1000

Date where running total is > then stock is 29.06.2023

Material	stock	out of stock by date
100000	1000	29.06.2023 00:00

any idea how to solve this?

[Anonymous]

Hi @sekka_1985 ,

I created a sample pbix file(see the attachment), please find the details in it.

1. Create a what-if paramater

Stock = GENERATESERIES(1, 8000, 1)

2. Create a measure as below

out of stock by date = 
VAR _selstock =
    SELECTEDVALUE ( 'Stock'[Stock] )
RETURN
    CALCULATE (
        MIN ( 'demand'[Calendar Day] ),
        FILTER ( 'demand', 'demand'[runningTotal_] >= _selstock )
    )

3. Create a table visual as below screenshot

Best Regards