Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Thank you, I did it other way but your concept helped me a lot.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |