First date based on value

[Anonymous]

Hey,

I think this should be an easy one. Work with R&D data.

I am trying to flag the first date in which a product moved through stages of development.

The data contains dates, product codes and stages. Looks like this:

Date	Product code	Stage
21-11-19	Test1	A
21-11-19	Test2	A
21-11-19	Test3	A
21-11-19	Test4	A
22-11-19	Test1	A
22-11-19	Test2	B
22-11-19	Test3	A
22-11-19	Test4	A
23-11-19	Test1	A
23-11-19	Test2	B
23-11-19	Test3	B
23-11-19	Test4	A

And I would like a column that returned this:

Date	Product code	Stage	First date on stage B
21-11-19	Test1	A	null
21-11-19	Test2	A	null
21-11-19	Test3	A	null
21-11-19	Test4	A	null
22-11-19	Test1	A	null
22-11-19	Test2	B	22-11-19
22-11-19	Test3	A	null
22-11-19	Test4	A	null
23-11-19	Test1	A	null
23-11-19	Test2	C	22-11-19
23-11-19	Test3	B	23-11-19
23-11-19	Test4	A	null

I guess it has to involve firstdate function in DAX, but nothing I've tried so far has worked at all.

Any help would be appreciated.

Thanks!

[Anonymous · Accepted solution]

@Anonymous 

You would need to add some conditions if you want get the First Change of the Stage (to Stage B): 

Result =
VAR previousstage =
    CALCULATE (
        MIN ( 'Table'[Stage] ),
        ALLEXCEPT ( 'Table', 'Table'[Product code] ),
        'Table'[Date] < EARLIER ( 'Table'[Date] )
    )
VAR FirstChangeToB =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[Stage] = "B" ),
        FILTER ( 'Table', 'Table'[Product code] = EARLIER ( 'Table'[Product code] ) )
    )
RETURN
    IF (
        NOT ISBLANK ( previousstage )
            && previousstage <> 'Table'[Stage],
        FirstChangeToB,
        BLANK ()
    )

 

Best,
Paul

[hnguy71]

Hi @Anonymous ,
Here's a quick and dirty version to get you started. I think there's a better way of accomplishing this but I haven't had my coffee this morning yet. As a calculated column:

Date of Change = 
var _FirstDate = CALCULATE(MIN([Date]), FILTER(RnD, [Stage] < EARLIER([Stage]) && [Product code] = EARLIER([Product code])))
var _SecondDate = CALCULATE(MIN([Date]), FILTER(RnD, [Stage] = EARLIER([Stage]) && [Product code] = EARLIER([Product code])))
RETURN
IF(NOT ISBLANK(_FirstDate) && _SecondDate = [Date], [Date], BLANK())