Hello @eobrie22 ,
Let's see if I got this right...
You start with a collection of bookings. Each booking is a record containing a start date and an end date.
You want to get a collection of every date whose day is either saturday or sunday for each booking, starting from the start date and ending at the end date of said booking.
Let's go step by step.
First, we know we can determine if a week day is monday, tuesday, wednesday, etc. thanks to the "WeekDay()" function which works like this:
| Day | Value |
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
So everytime we compute a date, we can check if it's a weekend if "WeekDay()" returns either "1" or "7".
Second, to compute a range of date, we need to know three things:
- The starting date of the range
- The ending date of the range
- The number of days between start and end
As you already know the start and end date, we only need to calculate the number of days between both with "DateDiff()".
This will help us define a "Sequence()", which will return a table with incremental numbers starting from 1 to the number we gave it.
So if we give it "5", "Sequence()" will return [ 1 ; 2 ; 3 ; 4 ; 5 ].
Then if we give it the number of days between start and end date, "Sequence()" will give us an incremental list of number equivalent to the range from start to end.
With this, we can mix-up "ForAll()", "Sequence()" and "DateAdd()" to quickly create a table of incremental date starting from start date up to end date.
ForAll(
Sequence(
DateDiff(StartDate, EndDate)
),
DateAdd(StartDate, Value, Days)
)
All you need now is to apply a "Filter()" to remove any date which "WeekDay()" is neither "1" or "7".
This should give you a strong head up. I hope this was helpful to you.
We'll be happy to assist if you still meet some difficulties.
