Forum Discussion
EWBWEBB
Helper III
Helper IIIMAX Date date not working in variable
I have 2 tables, one which contains all my people information the other table contains their sick days and reasons. StaffAbsence contains the following Absence Key, Date, PersonRef, Period A...
lbendlin
Super User
Super UserPlease provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
EWBWEBBHelper III
Helper IIIlbendlin - thank you.
These two tables would be joined on the absence key.
Absence
| AbsenceKey | Date | PersonRef | Period |
| 123452022-11 | 28/10/2022 | 12345 | 2022-11 |
| 123452022-11 | 29/10/2022 | 12345 | 2022-11 |
| 123452022-11 | 30/10/2022 | 12345 | 2022-11 |
| 123452022-11 | 31/10/2022 | 12345 | 2022-11 |
| 123452022-11 | 01/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 02/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 03/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 04/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 05/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 06/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 07/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 08/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 09/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 10/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 11/11/2022 | 12345 | 2022-11 |
| 123452022-11 | 12/11/2022 | 12345 | 2022-11 |
| 101112022-11 | 28/10/2022 | 10111 | 2022-11 |
| 101112022-11 | 29/10/2022 | 10111 | 2022-11 |
| 101112022-11 | 30/10/2022 | 10111 | 2022-11 |
| 101112022-11 | 31/10/2022 | 10111 | 2022-11 |
| 101112022-11 | 01/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 02/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 03/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 04/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 05/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 06/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 07/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 08/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 09/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 10/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 11/11/2022 | 10111 | 2022-11 |
| 101112022-11 | 12/11/2022 | 10111 | 2022-11 |
| 101112022-10 | 15/10/2022 | 10111 | 2022-10 |
| 101112022-10 | 14/10/2022 | 10111 | 2022-10 |
| 101112022-10 | 13/10/2022 | 10111 | 2022-10 |
| 101112022-10 | 12/10/2022 | 10111 | 2022-10 |
| 101112022-10 | 11/10/2022 | 10111 | 2022-10 |
| 123452022-10 | 15/10/2022 | 12345 | 2022-10 |
| 123452022-10 | 14/10/2022 | 12345 | 2022-10 |
| 123452022-10 | 13/10/2022 | 12345 | 2022-10 |
| 123452022-10 | 12/10/2022 | 12345 | 2022-10 |
| 123452022-10 | 11/10/2022 | 12345 | 2022-10 |
People
| AbsenceKey | PersRef | Period | StartDate | LeaveDate | FirstDateofPeriod | LastDayofPeriod |
| 123452022-11 | 12345 | 2022-11 | 01/01/2000 | 11/12/2022 | 16/10/2022 | 12/12/2022 |
| 101112022-11 | 10111 | 2022-11 | 01/01/2000 | 16/10/2022 | 12/12/2022 | |
| 101112022-10 | 10111 | 2022-10 | 01/01/2000 | 18/09/2022 | 15/10/2022 | |
| 123452022-10 | 12345 | 2022-10 | 01/01/2000 | 11/12/2022 | 18/09/2022 | 15/10/2022 |
The output I would expect would be:
In Period 2022-10
12345 - would count 5 days
10111 - would count 5 days
In Period 2022-11
12345 - Would count 15 days (with the 12/11/2022 not being counted as it is after the leave date)
10111 - Would be 16 days (Counting the 12/11/2022 as they do not have a leave date)