Calculated Column Assistance "Customer Contract State"

[Anonymous]

Good afternoon Team,

I am looking for assistance in writing the DAX necessary to create a calculated column to help determine if a customer is lost or active and when.  Here is the context of my data:

 My table is of each contract in our database.  The table contains columns for Corp-Name, Site-name, Site-ID, Contract-Date, Contract-Value, Contract-Expiration value.  It's important to understand that a Site can have multiple contracts, and a customer can have multiple sites.  

My goal for the new columns is if a customer has at least one active contract, at any site, then all rows for that customer must equal ACTIVE.  Only once all contracts for all sites have expired are they to be calculated as LOST.  Once a customer is LOST, I want a second column to be stripped with the MAX Expiration date of all the contracts for the sites of that customer.  If active, then remain blank.  Below is a visual of what I would expect the table to look like with the two new columns.  

With these two new columns, I intend to count them and graph them based on lost date and show the sum of their lost contract values.  I need them in column form for page filtering.

Here is how I attempted to calculate the CUSTOMER STATE, however, it is keeping context to the individual rows, and splitting the output for customers who are not truly lost but creating a row for the lost contracts and the active contracts.

Instead of below, this example should be "NOT LOST" instead of both.  

Thank you in advance for your support in helping me get this customer status and lost date resolved!

[speedramps · Accepted solution]

H Smoody

Pleaase consider ths solution and leave kudos

Here are some DAX measure you can tweak ....

AllContractsForCustomer =
/*
This measure counts all contracts for a customer.
The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context
*/
CALCULATE (
COUNTROWS(Contracts),
All (Contracts),
VALUES(Contracts[Customer])
)

ActiveContractsForCustomer =

/*
This measure counts all active contracts for the customer.
The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context.
Then is just filters ACTIVE
*/
CALCULATE (
COUNTROWS(Contracts),
All (Contracts),
VALUES(Contracts[Customer]),
Contracts[Contract status]="ACTIVE"
)

InactiveContractsForCustomer =

/*
This measure counts all inactive contracts for the customer.
The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context.
Then is just filters INACTIVE
*/
CALCULATE (
COUNTROWS(Contracts),
All (Contracts),
VALUES(Contracts[Customer]),
Contracts[Contract status]="INACTIVE"
)

I assume you just needed help overriding the row context. and you can do the rest from now on, because you seem to have a good grasp of IF logic.

[parry2k · Accepted solution]

@Anonymous add two calculated columns

Lost Y-N = 
VAR __countActive = 
CALCULATE ( 
COUNTROWS ( Table ), 
ALLEXCEPT ( Table, Table[Customer] ),
Table[Contract State] = "Active"
)
RETURN
IF ( __countActive >= 1, "Active", "Inactive" )

Most Recent Expiration Date = 
IF ( Table[Lost Y-N] = "Inactive", 
CALCULATE ( 
MAX ( Table[Expiration Date]), 
ALLEXCEPT ( Table, Table[Customer] )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[speedramps]

Yes Parry, ALLEXCEPT is better than my suggestion

[speedramps · Accepted solution]

H Smoody

Pleaase consider ths solution and leave kudos

Here are some DAX measure you can tweak ....

AllContractsForCustomer =
/*
This measure counts all contracts for a customer.
The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context
*/
CALCULATE (
COUNTROWS(Contracts),
All (Contracts),
VALUES(Contracts[Customer])
)

ActiveContractsForCustomer =

/*
This measure counts all active contracts for the customer.
The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context.
Then is just filters ACTIVE
*/
CALCULATE (
COUNTROWS(Contracts),
All (Contracts),
VALUES(Contracts[Customer]),
Contracts[Contract status]="ACTIVE"
)

InactiveContractsForCustomer =

/*
This measure counts all inactive contracts for the customer.
The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context.
Then is just filters INACTIVE
*/
CALCULATE (
COUNTROWS(Contracts),
All (Contracts),
VALUES(Contracts[Customer]),
Contracts[Contract status]="INACTIVE"
)

I assume you just needed help overriding the row context. and you can do the rest from now on, because you seem to have a good grasp of IF logic.

[Anonymous]

Thanks a ton!  You hit the nail on the head for the issue.  I completely forgot to get the except statement in there.