Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find the next Stop Date

Hi,

 

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

 

PositionTypeStart Date
10End07.11.2019 23:58
10End07.11.2019 23:57
10Start07.11.2019 23:57
10Start07.11.2019 23:56
20End07.11.2019 23:55
20Start07.11.2019 23:54
10End07.11.2019 23:54
20End07.11.2019 23:53
10Start07.11.2019 23:52
10End07.11.2019 23:50

 

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

 

PositionTypeStart DateIndex
10End07.11.2019 23:584
10End07.11.2019 23:573
10Start07.11.2019 23:573
10Start07.11.2019 23:562
20End07.11.2019 23:552
20Start07.11.2019 23:541
10End07.11.2019 23:542
20End07.11.2019 23:531
10Start07.11.2019 23:521
10End07.11.2019 23:501

 

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...

 

PositionTypeStart DateIndexEnd Date
10End07.11.2019 23:584 
10End07.11.2019 23:573 
10Start07.11.2019 23:57307.11.2019 23:57
10Start07.11.2019 23:56207.11.2019 23:54
20End07.11.2019 23:552 
20Start07.11.2019 23:54107.11.2019 23:53
10End07.11.2019 23:542 
20End07.11.2019 23:531 
10Start07.11.2019 23:52107.11.2019 23:50
10End07.11.2019 23:501 

 

But I like to get this

 

PositionTypeStart DateLike to get
10End07.11.2019 23:58 
10End07.11.2019 23:57 
10Start07.11.2019 23:5707.11.2019 23:58
10Start07.11.2019 23:5607.11.2019 23:57
20End07.11.2019 23:55 
20Start07.11.2019 23:5407.11.2019 23:55
10End07.11.2019 23:54 
20End07.11.2019 23:53 
10Start07.11.2019 23:5207.11.2019 23:54
10End07.11.2019 23:50 

 

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

 

Best regards

Timo

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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