Forum Discussion
Solved
Run-out Date Calculation
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:
- Create Date table and established relationship between date table & Purchase Coverage table
- 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!
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.
5 Replies
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.
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 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.
