How to lookup and return value from table 2 based on conditions in table 1 in Power BI desktop?

[Tooba_kazmi]

Hi All, 

I have two tables in Power BI, Table 1 has 3 date columns and Table 2 is the Date table. 

I want to create a unique column "Week Ending" in Table 1 based on those 3 date columns. 

Lead Created date, Actual Quote date & Processing date are independent date columns and are selected individually to get the total count in a date range. For example, for week ending 08/09/23, lead created date is filtered from 02/09 until 08/09 to find total count of leads. Then actual quote date is selected for same date range to find total quotes. The same is done for processing date. 

What is the best way to lookup Week Ending dates from the Date Table based on the condition that any one of the date columns are inside the date range?

For example, Return Week Ending from Table 2 if Lead Created Date OR Actual Quote Date OR Processing Date in Table 1 is within a date range of Week Starting 02/09/23 and Week Ending 08/09/23.  

Table 1:

LEAD_CREATED_DT	ACTUAL_QUOTE_DT	PROCESSING_DT
2-Sep-23	2-Sep-23	2-Sep-23
30-Aug-23	4-Sep-23	4-Sep-23
27-Jul-23	7-Sep-23	7-Sep-23
2-Sep-23	2-Sep-23	2-Sep-23
25-Aug-23	28-Aug-23	7-Sep-23
3-Sep-23	3-Sep-23	3-Sep-23
29-Jun-23	17-Jul-23	6-Sep-23
28-Aug-23	29-Sep-23	8-Sep-23
3-Sep-23	3-Sep-23	3-Sep-23

Table 2:

Date	Week Ending	Week Starting
8-Sep-23	8-Sep-23	2-Sep-23
7-Sep-23	8-Sep-23	2-Sep-23
6-Sep-23	8-Sep-23	2-Sep-23
5-Sep-23	8-Sep-23	2-Sep-23
4-Sep-23	8-Sep-23	2-Sep-23
3-Sep-23	8-Sep-23	2-Sep-23
2-Sep-23	8-Sep-23	2-Sep-23

Desired Result:

LEAD_CREATED_DT	ACTUAL_QUOTE_DT	PROCESSING_DT	Week Ending
2-Sep-23	2-Sep-23	2-Sep-23	8-Sep-23
30-Aug-23	4-Sep-23	4-Sep-23	8-Sep-23
27-Jul-23	7-Sep-23	7-Sep-23	8-Sep-23
2-Sep-23	2-Sep-23	2-Sep-23	8-Sep-23
25-Aug-23	28-Aug-23	7-Sep-23	8-Sep-23
3-Sep-23	3-Sep-23	3-Sep-23	8-Sep-23
29-Jun-23	17-Jul-23	6-Sep-23	8-Sep-23
28-Aug-23	29-Sep-23	8-Sep-23	8-Sep-23
3-Sep-23	3-Sep-23	3-Sep-23	8-Sep-23

[123abc]

To create a unique column "Week Ending" in Table 1 based on the conditions you specified, you can use Power BI's DAX language. You'll need to use DAX functions like FILTER, MAX, and RELATED to achieve this. Here are the steps to do this:

Assuming your Table 1 is named "Data" and Table 2 is named "DateTable":

1. In Power BI Desktop, go to the "Model" view.

2. Select "Data" (Table 1).

3. In the "Modeling" tab, click on "New Column."

4. Enter the following DAX formula to create the "Week Ending" column in Table 1:

Week Ending =
VAR SelectedWeek =
MAXX (
FILTER (
'DateTable',
'DateTable'[Date] >= Data[LEAD_CREATED_DT]
&& 'DateTable'[Date] <= Data[ACTUAL_QUOTE_DT]
&& 'DateTable'[Date] <= Data[PROCESSING_DT]
),
'DateTable'[Date]
)
RETURN
SelectedWeek

This formula finds the maximum (latest) date from Table 2 ("DateTable") where any of the three date columns from Table 1 fall within the date range specified.

1. Press Enter to create the new column. The "Week Ending" column in Table 1 will now contain the desired values.

Now, when you use Table 1 in your report, you can include the "Week Ending" column to get the desired result based on the conditions you specified.

[Tooba_kazmi]

Thanks that was a quick repsonse. The code below works with a little change in bold:

Week Ending =
VAR SelectedWeek =
MAXX (
FILTER (
'DateTable',
'DateTable'[Date] >= Data[LEAD_CREATED_DT]
&& 'DateTable'[Date] <= Data[ACTUAL_QUOTE_DT]
&& 'DateTable'[Date] <= Data[PROCESSING_DT]
),
'DateTable'[Week Ending]
)
RETURN
SelectedWeek

How will the code modify if there are blanks in the rows sometimes? 

LEAD_CREATED_DT	ACTUAL_QUOTE_DT	PROCESSING_DT
27-Jul-23		29-Jul-23
31-Jul-23

[123abc · Accepted solution]

If you have the possibility of blank or empty values in your date columns in Table 1, you should modify the DAX formula to handle these cases. You can use the ISBLANK function to check if a date column is blank before applying the filtering logic. Here's the modified DAX formula:

Week Ending =
VAR SelectedWeek =
MAXX (
FILTER (
'DateTable',
(
(ISBLANK(Data[LEAD_CREATED_DT]) || 'DateTable'[Date] >= Data[LEAD_CREATED_DT])
&&
(ISBLANK(Data[ACTUAL_QUOTE_DT]) || 'DateTable'[Date] <= Data[ACTUAL_QUOTE_DT])
&&
(ISBLANK(Data[PROCESSING_DT]) || 'DateTable'[Date] <= Data[PROCESSING_DT])
)
),
'DateTable'[Week Ending]
)
RETURN
SelectedWeek

In this modified formula, we use the ISBLANK function to check if a date column in Table 1 is blank. If a column is blank, it will not apply the corresponding condition, allowing the filtering to work correctly when there are blanks in the rows.

Now, this formula will correctly calculate the "Week Ending" even if some of the date columns in your data contain blanks.

[Tooba_kazmi]

Thanks! That's awesome!

[123abc]

You are wellcome.