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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
sekka_1985
Regular Visitor

out of stock by date

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 DaydemandrunningTotal_
10000028.04.2023 00:00170170
10000015.05.2023 00:00255425
10000023.05.2023 00:00255680
10000015.06.2023 00:0085765
10000022.06.2023 00:0085850
10000029.06.2023 00:004251275
10000019.07.2023 00:003401615
10000020.07.2023 00:002551870
10000003.08.2023 00:001702040
10000017.08.2023 00:002552295
10000024.08.2023 00:001702465
10000028.08.2023 00:00852550
10000007.09.2023 00:002552805
10000020.09.2023 00:001702975
10000005.10.2023 00:002553230
10000011.10.2023 00:001703400
10000025.10.2023 00:002553655
10000009.11.2023 00:001703825
10000020.11.2023 00:001703995

 

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 stockout of stock by date
100000100029.06.2023 00:00

 

any idea how to solve this?

 

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

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

vyiruanmsft_0-1682921516473.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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