Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Link two separate sets of dates to create a range, to allow slicer and filter

Hi there,


New to PowerBI and trying to resolve an issue - I haven't been able to find any resources or other forum topics that answer the issue.


I am creating an annual Leave report for my organisation, the purpose is to allow managers to see when people in the organisation have taken or not taken annual leave. Managers want to be able to see who is on leave in a given range or on a specific date, which they can set dynamically.


I have a table (Table A) showing the dates people in my organisation have taken annual leave. This contains columns with information about the personnel, and two separate columns, one for the leave start date, and one for the leave end date. Like this:


Employee Name DepartmentAbsence Start DateAbsence End Date
John SmithTeam 1Tuesday, 12 April 2022Sunday, 24 April 2022
John SmithTeam1Monday, 23 May 2022Sunday, 29 May 2022
Juliet SmytheTeam 2Saturday, 6 August 2022Sunday, 28 August 2022


Note that each person in the organisation has multiple rows - one each for each instance of annual leave (absence) taken.


I have created a Date Table (not in Query - so I'm not able to Append) called DateRange showing all dates in the years 2021-2025 - I want to allow the report to run on past the dates set in the date ranges contained in Table A as this report will continue to be used over time, not as a snapshot. I have created a relationship between DateRange and Table A, where Date (in DateRange) is linked to both Absence Start Date and Absence End Date. I have set a slicer and a filter (to try both options out) - this now allows me to filter effectively, but only shows dates actually contained within either of the two absence columns (start or end).


I can't work out how to set a slicer or filter allowing the user to select a date range or specific date - if I understand the way the system works correctly, effectively BI doesn't know that I want it to identify the dates contained between Absence Start and Absence End as a range, instead it sees them as to separate standalone dates.


I've tried creating a date range column, but couldn't find any guides on how to do this with DAX or Query editor. I tried using New Column from Examples - the system understoon that I was pairing the two columns, but not that they now represented dates in a range (it formatted the column as text and wouldn't allow to format as dates).


I'm sure I've missed a glaringly obvious tool - apologies if so. I'd appreciate your help!


N.B. I created DateRange using =CALENDAR. Since I want this report to be able to be used in perpetuity (beyond 2025), is it possible to create a similar date range encompassing the minimum to maximum dates contained in Table A Start Date and End Date columns, to avoid having thousands of rows in the DateRange table? I looked at using =CALENDARAUTO but it looked like this only populates the dates contained in Start Date /End Date, and not all the dates between. 



Thank you very much Amitchandak - that solved my issue, super helpful!

Helpful resources

Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

Top Solution Authors