Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

First date calculation when status is first reached

Hello,

 

I am trying to figuring out how to calculate the first date an ID has reached a certain status.

What do I mean with this?

 

I have a daily overview of multiple IDs with statusses and every day all the IDs have a status mentioned.

Now I have combined all the daily reports that where collected since the start of my project, and now I want to calculate the first date a certain ID had a certain status.

 

E.g.

Date                      ID                          Status

20-8-20191111Open
21-8-20191111Closed
22-8-20191111Closed
23-8-20191111Closed
24-8-20191111Closed
25-8-20191111Closed
20-8-20192222Open
21-8-20192222Open
22-8-20192222Open
23-8-20192222Open
24-8-20192222Closed
25-8-20192222Closed
20-8-20193333Closed
21-8-20193333Closed
22-8-20193333Closed
23-8-20193333Closed
24-8-20193333Closed
25-8-20193333Closed
20-8-20194444Open
21-8-20194444Open
22-8-20194444Open
23-8-20194444Open
24-8-20194444Open
25-8-20194444Open

 

I would like to only have the IDs with the first time the status was on closed:

 

21-8-20191111Closed
24-8-20192222Closed
20-8-20193333Closed

 

Can somebody please me with this?

Thank you :)

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try this

     

    Max Date = 
CALCULATE(
MIN( Table[Date] ),
ALLEXCEPT( Table, Table[Id] ),
Table[Status] = "Closed"
)

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous try this

     

    Max Date = 
CALCULATE(
MIN( Table[Date] ),
ALLEXCEPT( Table, Table[Id] ),
Table[Status] = "Closed"
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you! This really helped :)