get start of X event which finished first and apply to the event starting next

[Liwi]

Hi All,
I am hoping someone can help me with DAX for below problem
I need to get start of the event which finished the earliest, in the row corresponding to the event starting next.

Example below

Event C finishing first therefore start of event C is applied to event starting next which is event F

Event A finishing as second and the start of A event is applied to the event G which is starting next after C event.

I don't even know where to start with it 

Appreciate any advice

Thank you in advance 

 

[v-yulgu-msft · Accepted solution]

Hi @Liwi,

Please refer to below DAX formula:

Index =
IF (
    Event[Start/End] = "Start",
    IF (
        Event[Datetime]
            <= MINX ( FILTER ( Event, Event[Start/End] = "End" ), Event[Datetime] ),
        BLANK (),
        RANKX (
            FILTER (
                Event,
                Event[Start/End] = "Start"
                    && Event[Datetime]
                        >= MINX ( FILTER ( Event, Event[Start/End] = "End" ), Event[Datetime] )
            ),
            Event[Datetime],
            ,
            ASC,
            DENSE
        )
    ),
    RANKX (
        FILTER ( Event, Event[Start/End] = "End" ),
        Event[Datetime],
        ,
        ASC,
        DENSE
    )
)

New Start =
IF (
    Event[Start/End] = "Start",
    LOOKUPVALUE (
        Event[Datetime],
        Event[Index], Event[Index],
        Event[Start/End], "End"
    )
)

Best regards,

Yuliana Gu

[Liwi]

Oh wow.

Thank you so much Yuliana 

May I ask how did you get the index to appear this way?

[v-yulgu-msft]

Hi @Liwi,

Please see above DAX formula for [Index].

Regards,

Yuliana Gu

[Liwi]

Hello @v-yulgu-msft,

May I pick your brains again?

I have noticed that in situation where I have some event END between START of other ones it doesn't give me correct results as it is looking at MIN End from whole day
Is it possible for index of START event to start count all over after MAX of END which is < then current row?

Probably picture is best way to explain what I mean.

[Liwi]

sorry, missed that 😉

Assumed you did it in query editor.

Thanks lot for your help