Create a Table that only show the earliest date when my measure values go below zero

[Anonymous]

Sample data folder 

Hi Community!

I have attached a sample data file containing information about Part Numbers (A, B, C, D) and their respective stock quantities in the warehouse, the production quantity and the need-to-be-delivered quantity .

I have created a matrix visual below, which includes a quick measure called "Running Stock." This measure calculates the daily running total of stock, taking into account production and the quantity that needs to be shipped. (Stock+Production-the quantity that needed to be shipped )

Now, what I would like to do is create a table in Power BI that automatically take the earliest date when my running stock goes below 0.

like this:

Part Number	shortage dates
A	7/19/2023
B	7/17/2023
C	7/21/2023
D	7/23/2023

I appreciate your assistance with this. Thank you!

[johnyip]

@Anonymous Did that answer your question? If so, please mark my reply as the solution, so others with similar question can refer to this thread for their reference.

[johnyip]

Hi @Anonymous ,

To construct your desired table, first you need to create the below measure.

Running Stock on or below 0 = 
VAR CurrentDate = MAX(Sheet1[Date])
VAR CurrentDateMinus1 = CALCULATE(MAX(Sheet1[Date]),FILTER(ALLSELECTED('Sheet1'[Date]),'Sheet1'[Date] < CurrentDate))

VAR RunningStock1 = CALCULATE([Stock + production - Need to be shipped to customer],
							  FILTER(ALLSELECTED('Sheet1'[Date]),'Sheet1'[Date] <= CurrentDate)
                    )

VAR RunningStock2 = CALCULATE([Stock + production - Need to be shipped to customer],
							  FILTER(ALLSELECTED('Sheet1'[Date]),'Sheet1'[Date] <= CurrentDateMinus1)
                    )
VAR StagingTable1 = SUMMARIZE(FILTER(ALLSELECTED(Sheet1),[Date]=CurrentDate),
                              Sheet1[Part Number ], Sheet1[Date],
                              "Below 0", RunningStock1 <= 0)
VAR StagingTable2 = SUMMARIZE(FILTER(ALLSELECTED(Sheet1),[Date]=CurrentDateMinus1),
                              Sheet1[Part Number ], Sheet1[Date],
                              "Below 0", RunningStock2 <= 0)

VAR Result1 = MINX(FILTER(StagingTable1,[Below 0]=TRUE()),[Date])
VAR Result2 = MINX(FILTER(StagingTable2,[Below 0]=TRUE()),[Date])

RETURN
IF(AND(MAX([Date])=Result1, Result2 = BLANK()),1,0)

And then use this measure in the "filters on this visual" for your table, as below.

[Anonymous]

Hi @johnyip 

Thank you so much for your help,

however, when I tried it out on the actual data, the visual took literally forever to load and most of the time it just said "exceeded the available resources"

Hence, it would be great if there could be another, less complex method of doing it ?

Thank you!