Get Date from first negative Value of running stock

RZ89 — Wed, 16 Nov 2022 09:21:00 GMT

Hi everyone,

I have set up a running stock report to see at which time the stock is used up.

For that I am using a matrix visual and 2 DAX measures.

I need a custom DAX measure that gives back the date value for reaching the first negative running stock value for a material.

In This example it will be the 17.11 for Material 15M7068

Unfortunately I am not yet able to set this up..

Thanks for helping me! Best regards Ralph

Seq =

MINX (

FILTER (

SELECTCOLUMNS (

FILTER(ALLSELECTED(CA_MD04_ALL_REQUIREMENT_999_Tage),CA_MD04_ALL_REQUIREMENT_999_Tage[Material]=MAX(CA_MD04_ALL_REQUIREMENT_999_Tage[Material])),

"index", CA_MD04_ALL_REQUIREMENT_999_Tage[Req Date],

"rank", RANKX ( ALLSELECTED ( CA_MD04_ALL_REQUIREMENT_999_Tage ), CA_MD04_ALL_REQUIREMENT_999_Tage[Req Date],,ASC, DENSE )

[index] = MAX ( CA_MD04_ALL_REQUIREMENT_999_Tage[Req Date] )

[rank]

)

_____________________________________

Running_Stock = [Stock Value] - SUMX(TOPN( [Seq]

,FILTER(ALLSELECTED(CA_MD04_ALL_REQUIREMENT_999_Tage),CA_MD04_ALL_REQUIREMENT_999_Tage[Material]=MAX(CA_MD04_ALL_REQUIREMENT_999_Tage[Material]))

,CA_MD04_ALL_REQUIREMENT_999_Tage[Req Date],ASC)

,CALCULATE(SUM(CA_MD04_ALL_REQUIREMENT_999_Tage[Open Qty]))

)

__________________________

Re: Get Date from first negative Value of running stock

johnt75 — Wed, 16 Nov 2022 11:51:36 GMT

You can try

First negative date = FIRSTNONBLANK ( 'Date'[Date], IF ( [Running Stock] < 0, 1 ) )

RZ89 — Thu, 17 Nov 2022 07:43:42 GMT

Hi,

thank for the hint.

I set up that Dax measure, but I cannot drop it into my row of the matrix visual..