Run-out Date Calculation

[tobisw]

EDIT: How can i add a PBIX Testfile to that message?

Hello,

i´m working on a run-out date calculation in which we would like to see, on which date the stock coverage for different part number goes for the first time below zero.

My approach was following:

1. Create Date table and established relationship between date table & Purchase Coverage table
2. Created Cumulative Stock Coverage Measure with following code:

Cumulative Stock Coverage =

CALCULATE(

[Stock Coverage Measure running],

FILTER(ALLSELECTED('Date'[Date]),

'Date'[Date]<= MAX('Date'[Date])))

 3. For the conditional colour format i created a add. measure Colour Coding stock Coverage with following code:

Colour_Coding_Stock_Coverage =

IF(

[Cumulative Stock Coverage]>= 0,"",1)

4. Target what i would like to achieve is, to get a add. value/measure, which tells me, from which date on, our stock went for the first time below 0 (zero).

For below screenshot, the value/measure should show 10.12.2021 as the first run-out date.

Who could give me a hint how to calculate this date?

Thanks in advance!

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[tobisw]

Thanks for your hint. Please find below the relevant data for that topic - please note, that i entpivot the columns in the PowerQuery Editor.

Plant  (Local)	Material (Global)			Calendar Day	01.12.2021	02.12.2021	08.12.2021	09.12.2021	10.12.2021	15.12.2021	03.01.2022	05.01.2022	06.01.2022	12.01.2022	13.01.2022	19.01.2022	20.01.2022	26.01.2022	27.01.2022
M0/1	815	Purchase Part	Stock Coverage (EOB)	PCE	11.000	11.000	4.500	4.500	-3.500	-9.000	-9.000	-14.500	-14.500	-19.500	-19.500	-24.500	-24.500	-29.000	-29.000
M0/1	815	Purchase Part	Stock	PCE	11.000
M0/1	815	Purchase Part	Requirements	PCE			6.500		8.000	5.500		5.500		5.000		5.000		4.500
M0/1	815	Purchase Part	Supplier Orders	PCE		7.500		7.500			1.000		5.000		5.000		4.500		5.000
M0/1	815	Purchase Part	ASN

[lbendlin · Accepted solution]

Here would be a simplistic version

First Run Out = CALCULATE(min('Table'[Attribute]),'Table'[Value]<0,'Table'[Measure]="Stock Coverage (EOB)")

Without the involvement of the dates table yet (although that is always a good thing to have). see attached.

[tobisw]

Thanks a lot - is there a solution to create that date based on a measure too? 
Reason behind is, that in a 2nd step i would like to add the ASN quantities (these reflects parts in transit) to the calculation which would move the run out to a later date

[lbendlin]

Sorry should have specified that my sample code was for a measure.