I have a SharePoint calendar with recurring events for the next 30 years as a Master Staff Schedule. I'm looking for a way to find all dates between the Event Date and the End Date and then create a Date Picker to find the roster on the specified date. The staff move on a specific rotation, so for some, week 1 and week 2 are different, so that is why I used the recurring event method.
I created a Date Dimension from 2018 - 2048 that has expanded all the dates, including the month name, the day of the week, the name of the day, the day of the year...I just can't figure out how to create the connection between the recurrence (i.e. every fifth Monday) and the actual date stored in the date dimension.
Please share sample data (table view loaded into Power BI desktop) and show us your desired output to help us better understand your requirement.
Here is the calendar...for all the names in Title field, I have recurring events that show the Start Date (EventDate) and the End Date. There is a column that captures the Recurrence as TRUE. What I'm looking for is a way to expand all the dates between the start and end date so that if I want to create a date picker with today's date, it can show me who is where on this specific date.
Update - I found a way to expand all dates between two dates (easy) and I just found the calendar recurrence data
<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><weekly mo="TRUE" weekFrequency="1" /></repeat><repeatInstances>100</repeatInstances></rule></recurrence>
Now the question is, how can I expand all dates between two dates while applying the recurrence rule? I don't want all dates, but every Monday between those dates.