Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Icon for Helper IV rankHelper IV
3 years ago
Solved

Calculating date range of availability

Hi

I am attempting to display a date range of the next availability for an employee relative to todays date

 

I have 2 tables

1) A Fact table detailing employee id and availability

2) A Date table

 

DateEmployee IDAvailability Status

07/05/2023

1Available
08/05/20231Available
09/05/20231Available
10/05/20231Available
11/05/20231On Project
12/05/20231On Project
13/05/20231On Project
14/05/20231On Project
15/05/20231Available
16/05/20231Available
17/05/20231Available
18/05/20231Available
19/05/20231Available
20/05/20231Available
21/05/20231Available
22/05/20231Available
23/05/20231Available
24/05/20231On Project
25/05/20231On Project
26/05/20231On Project
27/05/20231On Project
28/05/20231Available
29/05/20231Available
30/05/20231Available

 

If todays date is 16/05/2023 I need to produce a visualisation that shows the following (the data is as above).

 

Employee IDAvailable Date StartAvailable Date Finish
115/05/202323/05/2023

 

If todays date is 21/05/2023 I need to produce a visualisation that shows the following (the data is as above).

 

Employee IDAvailable Date StartAvailable Date Finish
115/05/202323/05/2023

 

If todays date is 28/05/2023 I need to produce a visualisation that shows the following (the data is as above).

 

Employee IDAvailable Date StartAvailable Date Finish
128/05/202330/05/2023

 

Any help would be appreciated. Thanks

Need Help