Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

First date based on value

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 co...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
hnguy71
Super User
6 years ago

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())