Plotting dates into a calendar table

[krisslax]

So, I have an absence table, which I'd like to load into a calendar type table plotting how much resource we have available on a particular day.

Absence Table:

EMP_NO	DATE_FROM	DATE_TO	DURATION
1	01/01/2023	03/01/2023	3
2	03/01/2023	04/01/2023	2
3	02/01/2023	02/01/2023	1
1	15/02/2023	15/02/2023	1
2	22/02/2023	23/02/2023	2
3	23/02/2023	25/02/2023	3

My plan is to load each date in the next 2 weeks from today from a master calendar.  So if I was to pick a particular date from this calendar on chart/filter, a table will be able to show me how many people are on annual leave.

So for example from the table above:

On the 01/01/2023, it would show Employee 1 on leave.

On the 02/01/2023, it would show Employees 1 and 3 on leave.

I've been playing with the data but honestly don't even know where to start with it.

[PrashantSangle · Accepted solution]

you have to generate the records which comes under that date range

try below

Load EMP_NO, DATE_FROM, DATE_TO, date(DATE_FROM + IterNo() - 1) as ABSENCE_DATE 

From table_name

while DATE_FROM + IterNo() - 1 <= DATE_TO;

Regards,

Prashant Sangle

[krisslax]

Prashant - that's fantastic, thank you so much, that really put things in perspective for me and gives me a great start to this analysis.  Really appreciate you taking the time to provide the solution.