check if value in column is present in other column and return a value on the bases of that

[Anonymous]

Hey all, below is a table I am working on....

I want to check if the column "Make Up Service completion date" contains the date from "due date" column. and if it does I want to return a string. I also want to check if the "Make Up Service completion date" column contains a date before the date in 'due date' column, so for the given table I want to check if 11/15 and 11/16 are present in the "Make up service completion date" column on the bases of "Due date " column. 

Now I am thinking I can use the query:

if(contains(table,Make up service completion date,"11/16/2019"), "True string", "False string")

but the issue is that this table has different due date scorlling down and I do not want to hard code the value "11/16/2019" in the DAX query. 

Due Date	Service make up deadline	Make Up Service completion date
11/16/2019 1:00 PM	11/18/2019	11/18/2019 6:03AM to 8:19AM
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019	11/17/2019 11:03AM to 1:17PM
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019	11/15/2019 8:30PM to 9:30PM
11/16/2019 1:00 PM	11/18/2019	11/14/2019 9:28AM to 12:05PM
11/16/2019 1:00 PM	11/18/2019	11/16/2019 4:05AM to 6:15AM
11/16/2019 1:00 PM	11/18/2019	11/16/2019 8:02PM to 9:51PM
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019	11/18/2019 12:59AM to 2:42PM
11/16/2019 1:00 PM	11/18/2019	11/17/2019 11:28AM to 1:33PM
11/16/2019 1:00 PM	11/18/2019	11/17/2019 12:07AM to 1:47AM
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019	11/16/2019 11:18PM to 12:53AM
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019
11/16/2019 1:00 PM	11/18/2019	11/15/2019 8:00PM to 10:30PM
11/16/2019 1:00 PM	11/18/2019	11/16/2019 2:07AM to 3:10AM
11/16/2019 1:00 PM	11/18/2019	11/16/2019 8:25AM to 10:32AM
11/16/2019 1:00 PM	11/18/2019	11/16/2019 9:26AM to 10:22AM
11/16/2019 1:00 PM	11/18/2019	11/16/2019 9:20PM to 9:53PM
11/16/2019 1:00 PM	11/18/2019	11/16/2019 10:06PM to 11:03PM

[helassal · Accepted solution]

@Anonymous 

To achieve this, I suggest to prepare your columns first in the "Power Query Editor":

1- Split "Make Up Service completion date" by space delimiter, so you will get 4 columns, Date, TimeFrom, column containg value "to" and TimeTo.

2- Then Merge Make up Service Completion date part with each of the TimeFrom and TimeTo to have in the end two datetime columns "Make Up Service Completion Date From" and "Make Up Service Completion Date To".

Now you are ready to create calculated columns to compare dates:

This calculated column returns "TrueString" if the due date falls between Make Up Service Completion Dates

IsDueDateWithinMakeUpServiceDates = if('Table'[Due Date] >= 'Table'[Make Up Service completion date.dateTimeFrom] && 'Table'[Due Date] <= 'Table'[Make Up Service completion date.dateTimeTo], "TrueString","FalseString")

This calculated column returns "TrueString" if the due date is in the day before "Make Up Service Completion Date From"

IsDueDateInTheDayBeforeMakeUpServiceDatesFromDate = 
if('Table'[Due Date] >= DATEADD('Table'[Make Up Service completion date.date],-1,DAY) && 'Table'[Due Date] < 'Table'[Make Up Service completion date.date], "TrueString","FalseString")

This calculated column returns "TrueString" if any of the previous calculated columns returns "TrueString"

IsDueDateWithinMakeUpServiceDatesOrEarlierByOneDay = 
if('Table'[IsDueDateInTheDayBeforeMakeUpServiceDatesFromDate] = "TrueString" || 'Table'[IsDueDateWithinMakeUpServiceDates] = "TrueString","TrueString","FalseString")

Extra: This calculated column returns "TrueString" if the due date is before "Make Up Service Completion Date From"

IsDueDateBeforeMakeUpServiceDates = if('Table'[Due Date] < 'Table'[Make Up Service completion date.dateTimeFrom], "TrueString","FalseString")

You can use this sample file to follow the Power Query steps: Power BI Sample File 

Best Regards,

Haitham

[Anonymous]

Thank you, I did it other way but your concept helped me a lot.