Set value for each line of table based on two date fields

[BE_KMI]

Hallo,

I have a sales table where the customer id, and contract id is available and i have an contract table with the contract id and the cancelation date. They are connected via the contract id.

In addition i have an calendar dimension with no connection to the data model where i want to select one day. Therefore i created a slicer and use the minimum date:

I created a measure for the "Select Day": Select Day = min(Select Date[Date])

The dimension looks like this:

So the selected value is 01.01.2022.

What I want to do is to use the Select Day date to decicde if a customer is an "active" or "inactive" customer.

If cancelation date is greater or equal than Select Day it is an active customer.

If cancelation date is lower than Select Day it is an inactive customer.

Cancelation date is and status is not used in the report visual, i need the status only for filtering.

BR

Martin

[rautaniket0077]

Try this measure.

Measure  =

SWITCH(

    TRUE(),

    MAX(Contract Table[Cancellation Date]) >= [select_day],"Active","Inactive"

)

if it solve your issue accept my answer as solution

[BE_KMI]

Thank you for your answer. For the example it works fine.

I also tried it at the big data model and there the max cancellation date is wrong. 

As Example: for contract 0200275664 the cancellation date is 25.10.2019:

But when i use the max function for the cancellation date I get 31.12.9999:

For all rows in the visual, the cancellation date is 31.12.9999 and therefore all are active.

Do you have an idea why the behaviour is like that?

[rautaniket0077]

Hi @BE_KMI, please provide the sample pbix file.