topic Set value for each line of table based on two date fields in DAX Commands and Tips

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

BE_KMI — Thu, 13 Apr 2023 07:15:37 GMT

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.

Martin

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

rautaniket0077 — Thu, 13 Apr 2023 08:35:35 GMT

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

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

BE_KMI — Thu, 13 Apr 2023 12:38:51 GMT

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?

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

rautaniket0077 — Sun, 16 Apr 2023 08:02:30 GMT

Hi BE_KMI, please provide the sample pbix file.