Check if each date is between any of the multiple date ranges in another table

[F800]

Hi All, I'm using PowerQuery in PowerBI trying to validate if the badge swipe of a user falls between any of the data ranges in another table.

Table A has all the swipe records from an access control system (multiple per day, In and Out).

Table B contains each reservation for the users, considering that there will be multiple reservation date ranges for the same user with gaps in between them. Historic data is required to be kept so I can tell how many users with active reservation entered the facility on each day.

Also, if the date equals the start or end of the reservation, to produce an Arrival/Departure value in another column.

The orange columns is the result I'm looking for.

[FarhanJeelani · Accepted solution]

Dear  ,To achieve the desired results in Power Query, follow these steps:

Steps in Power Query:

1. Load Tables:

Load Table A (Swipe data) and Table B (Reservation data) into Power BI.

2. Expand Date Ranges in Table B:

Create a new table that expands the date ranges for each reservation in Table B. This ensures that each date within a reservation range is listed.

Steps:

Add a Custom Column to generate a list of dates between Start date and End date: = List.Dates([Start date], Duration.Days([End date] - [Start date]) + 1, #duration(1, 0, 0, 0))

Expand this list into rows using the Expand to New Rows option.

Rename this column as Expanded Date.

3. Merge Table A and Expanded Table B:

Perform a Merge Queries operation:

Select Table A (Swipe data) as the first table.

Select the Name column from Table A and Table B, and join on the Name column.

Add an additional condition to merge on Swipe date = Expanded Date.

4. Add Validation Columns:

Active Reservation?: If a match is found, set the value to "Yes," otherwise "No." = if [Expanded Date] = null then "No" else "Yes"

Arrival/Departure Day:

Add a conditional column to check if the Swipe date matches the Start date or End date of the reservation: = if [Swipe date] = [Start date] then "Arrival"

  else if [Swipe date] = [End date] then "Departure"

  else "No"

After the merge, expand Table B columns into Table A and create two new columns:

5. Clean Up the Columns:

Remove unnecessary columns, keeping only the ones you need (e.g., Name, Swipe date, Active Reservation?, Arrival/Departure Day).

6. Load Data into Power BI:

Close and load the transformed data into Power BI for reporting.

Result:

Active Reservation?: Identifies whether a swipe date falls within any reservation range.

Arrival/Departure Day: Indicates if the swipe date is the start or end date of a reservation.

Please mark this as solution if it helps you. Appreciate Kudos.

• @F800

[Anonymous]

Hi @F800 ,

Thanks for posting.

I thinks Ashish_Mathur's workaround is great and correct.

If your problem has been solved by his reply, please accept it as solution. More people will benefit from it in the future.

If you still have doubts, please feel free to tell us.

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

Active reservation? = if(CALCULATE(COUNTROWS('Swipe records'),FILTER('Swipe records','Swipe records'[Reservatio]=EARLIER(Reservation[Name])&&'Swipe records'[Start date]<=EARLIER(Reservation[Swipe date])&&'Swipe records'[End date]>=EARLIER(Reservation[Swipe date])))>0,"Yes","No")

Column = if(CALCULATE(min('Swipe records'[Start date]),FILTER('Swipe records','Swipe records'[Reservatio]=EARLIER(Reservation[Name])&&'Swipe records'[Start date]=EARLIER(Reservation[Swipe date])))=Reservation[Swipe date],"Arrival",if(CALCULATE(min('Swipe records'[End date]),FILTER('Swipe records','Swipe records'[Reservatio]=EARLIER(Reservation[Name])&&'Swipe records'[End date]=EARLIER(Reservation[Swipe date])))=Reservation[Swipe date],"Departure","No"))

Hope this helps.

[F800]

Hi Ashish, thanks for your contribution. Being fairly new to Pwer BI and related tools I'm not very familiar with calculated columns, I've tried the other solution expanding the date range and it does work. I would like to learn how to implement your suggested solution to try another approach. 

Could you please let me know where should I use the code? is this in Power BI desktop direclty or in Power Query? Thanks.

[Ashish_Mathur]

You are welcome.  Click on the second icon in the vertical panel (at the extreme left hand side) and click on the New Column button there.  Write the formulas there.

[FarhanJeelani · Accepted solution]

Dear  ,To achieve the desired results in Power Query, follow these steps:

Steps in Power Query:

1. Load Tables:

Load Table A (Swipe data) and Table B (Reservation data) into Power BI.

2. Expand Date Ranges in Table B:

Create a new table that expands the date ranges for each reservation in Table B. This ensures that each date within a reservation range is listed.

Steps:

Add a Custom Column to generate a list of dates between Start date and End date: = List.Dates([Start date], Duration.Days([End date] - [Start date]) + 1, #duration(1, 0, 0, 0))

Expand this list into rows using the Expand to New Rows option.

Rename this column as Expanded Date.

3. Merge Table A and Expanded Table B:

Perform a Merge Queries operation:

Select Table A (Swipe data) as the first table.

Select the Name column from Table A and Table B, and join on the Name column.

Add an additional condition to merge on Swipe date = Expanded Date.

4. Add Validation Columns:

Active Reservation?: If a match is found, set the value to "Yes," otherwise "No." = if [Expanded Date] = null then "No" else "Yes"

Arrival/Departure Day:

Add a conditional column to check if the Swipe date matches the Start date or End date of the reservation: = if [Swipe date] = [Start date] then "Arrival"

  else if [Swipe date] = [End date] then "Departure"

  else "No"

After the merge, expand Table B columns into Table A and create two new columns:

5. Clean Up the Columns:

Remove unnecessary columns, keeping only the ones you need (e.g., Name, Swipe date, Active Reservation?, Arrival/Departure Day).

6. Load Data into Power BI:

Close and load the transformed data into Power BI for reporting.

Result:

Active Reservation?: Identifies whether a swipe date falls within any reservation range.

Arrival/Departure Day: Indicates if the swipe date is the start or end date of a reservation.

Please mark this as solution if it helps you. Appreciate Kudos.

• @F800

[F800]

Farhan, really appreciate your reply as it did work as intended.

[FarhanJeelani]

Hi @F800 ,

Glad I could help! If this solved your issue, please mark it as the solution and give kudos—it helps others find the answer too. 😊