Filter ID's which have contract not active currently

[Seizera]

Hello,

I have a table where i have contracts which can be either "Ended" "Active" and they can have the same ID. Example data:

Start date	End date	Contract state	Contract ID
9.3.2020		Active	1
13.08.2019	8.3.2020	Ended	1

Like this. I want to have a list of contract ID's which have their last status as Ended. How can i do this?

Use case is as follows:

I have 20 000 contracts and i want to know which have ended, so they don't have a new active contract starting after the Ended row in database.

If i filter by Ended only, for example this contract ID 1 would show me that it only has a Ended row, when it does have an Active row aswell and thus is a running contract.

Hopefully i explained this properly, i could not make up a solution myself to a quite easy problem, still trying to learn the ropes of PowerBI.

Thanks in advance,

Mikael

[lkalawski · Accepted solution]

To do this, you need to perform several operations in Power Query:

1. With this data set, group it by ContractID and leave the maximum Start and End Date values for each ID.
   The result:
    
2. Then add a condition that checks whether Max (Start Date)> Max (End Date). If so, it means that the contract is still active. If not, it means it's over.
   
3. Then merged the data with your starting table (in blue), expand it and remove unnecessary columns.
4. The result:
   

[Seizera]

Hi!

Thanks for the quick reply.

I found this to be mostly working, only thing where it doesnt seem to work properly is a case like this:

startdate	enddate	active	contractid
25.2.2019 21:00:00	20.1.2020 22:00:00	ended	1
20.1.2020 22:00:00		active	1

For situation like this it seems like this contract would show up as non active but it is indeed active. Contracts should always have a time difference (1 day usually) but sometimes this is not the case.

Is there any possible solution to quickly filter these out aswell?
I was thinking of doing startdate = enddate => filter out, but would that work properly.

Thanks!

[lkalawski]

This change of condition will help. Especially when you compare datetime. If startdate> = end date it means that it is still active.

If I helped, please accept the solution! 🙂

Thanks!

[Seizera]

Hey,

It did, thanks for the help! Accepted the solution.