How to use 2 tables to generate a new column

[EaglesTony]

hi,

  I have a table with dates in it as follows:

Qtr          StartDate            EndDate

1             1/3/2024             3/26/2024

2             3/27/2024           7/2/2024

3             7/3/2024             9/24/2024

4             9/25/2024          12/31/2024

  I have another table that has a StartDate and EndDate

Key         StartDate            EndDate

ABC         7/1/2024           9/1/2024

DEF         3/28/2024          7/1/2024

GHI         9/25/2024          12/20/2024

What I need is the Qtr column on the 2nd table, so my output would look like this(Note: we don't want to consider those that span more than 1 quarter):

Key         StartDate            EndDate       QTR

ABC         7/1/2024           9/1/2024         N/A (spans multiple qtrs)

DEF         3/28/2024          7/1/2024         2

GHI         9/25/2024          12/20/2024     4

[DatawithDinesh · Accepted solution]

Hello @EaglesTony ,
you can use the below dax to achieve your desired result. 

QTR = 
Var matchingqtr= FILTER(qtr,qtr[StartDate ]<=data[ StartDate ] && qtr[EndDate]>=data[EndDate])
Var CountQtr=COUNTROWS(matchingqtr)
return IF(CountQtr=1,
MAXX(matchingqtr,qtr[Qtr ]),
"N/A")

Create a custom column in the data table.

I'm using Filter to filter the QuarterTable to only include rows where the [StartDate] in QuarterTable is less than or equal to [StartDate] in the data table and where the [EndDate] in QuarterTable is greater than or equal to [EndDate] in the data table.
Then using CountRows we can check if there's exactly one quarter that fits the date range. If so, it's within one quarter, then do
MAXX to retrieve the quarter number from the filtered QuarterTable for that specific row. else give N/A

[DatawithDinesh · Accepted solution]

Hello @EaglesTony ,
you can use the below dax to achieve your desired result. 

QTR = 
Var matchingqtr= FILTER(qtr,qtr[StartDate ]<=data[ StartDate ] && qtr[EndDate]>=data[EndDate])
Var CountQtr=COUNTROWS(matchingqtr)
return IF(CountQtr=1,
MAXX(matchingqtr,qtr[Qtr ]),
"N/A")

Create a custom column in the data table.

I'm using Filter to filter the QuarterTable to only include rows where the [StartDate] in QuarterTable is less than or equal to [StartDate] in the data table and where the [EndDate] in QuarterTable is greater than or equal to [EndDate] in the data table.
Then using CountRows we can check if there's exactly one quarter that fits the date range. If so, it's within one quarter, then do
MAXX to retrieve the quarter number from the filtered QuarterTable for that specific row. else give N/A

[EaglesTony]

This does work I had the >= and <= reversed!

[EaglesTony]

Ok I added the column to the data table, but giving incorrect results.

For example I have:

Key XyZ with 7/3/2024 as the start date and 8/6/2024 as the end date and it is giving me N/A, instead of 3.

Key AAA with 7/1/2024 as the start date and 9/30/2024 as the end date and it is giving me 3, instead of N/A

[DatawithDinesh]

Could you check your date format if it is correctly detecting the month and day. Cuz I just added your new data and I get correct result.

[EaglesTony]

Is there a way to also get the assocaited Start Date and End Date of the Quarter ?

[DatawithDinesh · Accepted solution]

Just use the same dax but just change the  QTR with Start and End date also replace N/A with blank() as it won't be able to handle date and string type.

QTRStartdate = 
Var matchingqtr= FILTER(qtr,qtr[StartDate ]<=data[ StartDate ] && qtr[EndDate]>=data[EndDate])
Var CountQtr=COUNTROWS(matchingqtr)
return IF(CountQtr=1,
MAXX(matchingqtr,qtr[StartDate ]),
BLANK())



QTRENDdate = 
Var matchingqtr= FILTER(qtr,qtr[StartDate ]<=data[ StartDate ] && qtr[EndDate]>=data[EndDate])
Var CountQtr=COUNTROWS(matchingqtr)
return IF(CountQtr=1,
MAXX(matchingqtr,qtr[EndDate]),
BLANK())

[EaglesTony]

1 final request, I have the following:

Key         StartDate            EndDate

ZZZ         3/27/2024           7/162024

It is picking 3/27/2024 as the Quarter Start Date(correct), but 7/2 as the Quarter End Date (which is incorrect), as it should pick 9/24/2024(the quarters end date for Q3).

[EaglesTony]

All my dates are "Data type: Date" and Format "03/14/2001 (mm/dd/yyyy).

Not sure if this matters, but some records don't have either a Start Date or End Date.

[DatawithDinesh]

If the data is not sensitive can you upload your pbix file I can check it in the file. Right now I'm not able to scope out as to what might be causing that issue. 

[DatawithDinesh]

That's weird it should allow you to refrence the data table inside the Filter. 
Can you send me the dax that you are writing in the Data table for qtr. 

[EaglesTony]

See my recent post..I was able to add the new column to the data table, but incorrect results.