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 | Department | Absence Start Date | Absence End Date |
| John Smith | Team 1 | Tuesday, 12 April 2022 | Sunday, 24 April 2022 |
| John Smith | Team1 | Monday, 23 May 2022 | Sunday, 29 May 2022 |
| Juliet Smythe | Team 2 | Saturday, 6 August 2022 | Sunday, 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.
