Finding the last date a discrepancy of stock happens

[09]

Hello all,

I'm working on a dax formula that takes in account all the particular cases that might happen. the formula will calculate the last date a discrepancy of stock for each material occurs. Thanks to this community, the current formula works perfectly in this case  below for example and it gives me as a result 21 june.

Date	Material Gap
23-juin	2
22-juin	2
21-juin	0
20-juin	0
19-juin	1
18-juin	0
17-juin	0
16-juin	1
15-juin	1

PS: the DAX formula is  

=var currentValue = SELECTCOLUMNS( TOPN(0; 'Table'; 'Table'[Date]; DESC); "@val"; 'Table'[Date])

return IF( currentValue = 0;

var lastNonGap = CALCULATE( MIN('Table'[Date]); 'Table'[Material Gap] <> 0)

return CALCULATE( MAX('Table'[Date]); 'Table'[Material Gap] = 0; 'Table'[Date]] < lastNonGap)

)

But, only in this case below  (The material is inbounded but with a gap: 2 parts in a system and 0 parts in the other one), i get no result (No date generated):

Date	Material Gap
23-juin	2
22-juin	2

Any suggetion to improve the formula so that it will take into account this particular case.

Thank you a lot for your help 🙂 

[Anonymous · Accepted solution]

Hi @09 ,

Please have a try. 

Create a measure.

Measure =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Material Gap] = 0 )
    )
RETURN
    IF ( _maxdate = SELECTEDVALUE ( 'Table'[Date] ), 1, BLANK () )

Or a column.

Column = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (  ( 'Table' ), 'Table'[Material Gap] = 0 )
    )
RETURN
    IF ( _maxdate =  ( 'Table'[Date] ), 1, BLANK () )

Then filter the measure or the column.

If I have misunderstood your meaning, please provide more details with your desired output.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

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

[09]

Hello @Anonymous 

Thank you for your help.

I'm actually working with power Query in excel.

I'm looking for the last time a stock gap between two systems has occurred for one material. The gap value is actually not a column but a dax measure. And the final result would be displayed in a pivot table. 

For now, this DAX formula works for me except for particular cases when i have older stock discrepancies so the formula returns the date of occurence of the first GAP that it has been regulated and what i really want is the last date the gap occured.

This is my Dax formula 

=CALCULATE(MIN('HISTORIQUE MB52'[Date]);FILTER(ALLEXCEPT('HISTORIQUE MB52';'HISTORIQUE MB52'[Material]);[Ecart 4400 UNR]<>0))

[Ecart 4400 UNR] this is a dax measure not a column that gives the value of stock GAP which is different from 0.

The two last rows, i have not a correct result: it gives me the date of the first GAP that  happened.

Material	GAP	Last GAP DATE
MATERIAL A	-1	11/03/2022
MATERIAL B	-1	20/01/2022
MATERIAL C	1	31/01/2022
MATERIAL D	-1	17/01/2022
MATERIAL E	-1	17/01/2022