Collect all dates within a date range

[eobrie22]

Hi,

I have a bit of a challenging situation here.

First I am collecting all booking requests that are of WorkWeekType.value="Weekends Only". 

The collection contains a start and end date that could span over multiple weeks.

I then need to collect all the dates in that range, for each booking, and get all the weekend dates only.

I then need to create new bookings for each item on just the weekend dates - I need to create new start and end dates and new booking for each weekend in the original date span and patch it back to col_combinedrequests. The issue is that I need to do this for multiple booking items at the same time.

Any help would be appreciated.

Thanks!

[WiZey · Accepted solution]

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:

1. The starting date of the range
2. The ending date of the range
3. 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.

[eobrie22]

@WiZey 

Thankyou!

How do I do that for each individual booking record? And then how do I create new bookings with just the weekend dates?

[eobrie22]

and what if the request start date and end date is the same day? How would I modify that formula

[eobrie22]

I figured it out. I just need help fixing your formula as its not including the start date itself in the date range collection

[WiZey]

Congrats on figuring this out! Now about your next inquiry.

By default, "Sequence()" first iteration is « 1 ». But you can change it by specifying from which number the function must start.

https://docs.microsoft.com/en-us/power-platform/power-fx/reference/function-sequence#syntax

Basically, you could tell "Sequence()" to start from « 0 » instead of « 1 ». But remember to re-add the offset in the range, otherwise your result will be one day short.

Re-using my previous formulae, it should look like this:

ForAll(
  Sequence(
    DateDiff(StartDate, EndDate) + 1,
    0
  ),
  DateAdd(StartDate, Value, Days)
)

I hope this was helpful to you.