Find the next Stop Date

[Anonymous]

Hi,

I try to find the next "Stop Date". I have got the following structure

Position	Type	Start Date
10	End	07.11.2019 23:58
10	End	07.11.2019 23:57
10	Start	07.11.2019 23:57
10	Start	07.11.2019 23:56
20	End	07.11.2019 23:55
20	Start	07.11.2019 23:54
10	End	07.11.2019 23:54
20	End	07.11.2019 23:53
10	Start	07.11.2019 23:52
10	End	07.11.2019 23:50

So I add an Index to column to share the positions and and the start or and type.

Position	Type	Start Date	Index
10	End	07.11.2019 23:58	4
10	End	07.11.2019 23:57	3
10	Start	07.11.2019 23:57	3
10	Start	07.11.2019 23:56	2
20	End	07.11.2019 23:55	2
20	Start	07.11.2019 23:54	1
10	End	07.11.2019 23:54	2
20	End	07.11.2019 23:53	1
10	Start	07.11.2019 23:52	1
10	End	07.11.2019 23:50	1

My idea was to add a second column and add the Date with type "End" in this column where Position = Position and Index = Index

But as you can see, I have got more End Dates than Start Dates. So in this case my and End Date is after my Start Date...

Position	Type	Start Date	Index	End Date
10	End	07.11.2019 23:58	4
10	End	07.11.2019 23:57	3
10	Start	07.11.2019 23:57	3	07.11.2019 23:57
10	Start	07.11.2019 23:56	2	07.11.2019 23:54
20	End	07.11.2019 23:55	2
20	Start	07.11.2019 23:54	1	07.11.2019 23:53
10	End	07.11.2019 23:54	2
20	End	07.11.2019 23:53	1
10	Start	07.11.2019 23:52	1	07.11.2019 23:50
10	End	07.11.2019 23:50	1

But I like to get this

Position	Type	Start Date	Like to get
10	End	07.11.2019 23:58
10	End	07.11.2019 23:57
10	Start	07.11.2019 23:57	07.11.2019 23:58
10	Start	07.11.2019 23:56	07.11.2019 23:57
20	End	07.11.2019 23:55
20	Start	07.11.2019 23:54	07.11.2019 23:55
10	End	07.11.2019 23:54
20	End	07.11.2019 23:53
10	Start	07.11.2019 23:52	07.11.2019 23:54
10	End	07.11.2019 23:50

Any ideas how to find the next Date with type End depending on the Position?

Best regards

Timo

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can use the following measure formula to achieve your requirement:

NextDate =
VAR currType =
    SELECTEDVALUE ( 'Table'[Type] )
RETURN
    IF (
        currType = "Start",
        CALCULATE (
            MIN ( 'Table'[Start Date] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Start Date] > MAX ( 'Table'[Start Date] ) ),
            VALUES ( 'Table'[Position] )
        )
    )

Regards,

Xiaoxin Sheng