Both WeekStarting and WeekEnding employ the same pattern, with only minor differences. The first line where the __WeekDay variable is set, is designed to be edited by the user. This sets the day on which a week begins or ends. This can vary by organization, as some organizations start their weeks on Sunday, while others start their weeks on Monday or even other days of the week. The next line sets the current working date and stores this in the __Date variable. We use this variable to get the week number and year of this date and store these values in the variables __WeekNum and __Year, respectively.
Next, we create a variable to house a temporary calendar table in the __Calendar variable. We start with a calendar table created by the CALENDAR function. For WeekStarting, we want this table to include all dates in the current working year as well as the previous year, since weeks may start in the previous year. For WeekEnding, we want all dates in the current working year as well as the subsequent year, since weeks may end in the next year. We use the ADDCOLUMNS function to add the week number and the weekday using the WEEKNUM and WEEKDAY functions.
Now that we have a temporary calendar table, we can use this table to attempt to look up the date in the same year and week number as our current working date. This date will have a weekday that corresponds with the weekday that starts or ends our week (__WeekDay). This is done by using FILTER to filter down to a single value and wrapping this filter clause with an iterative aggregator such as MINX. We store this date in the __WeekDate variable.
Because a week may start or end in the previous year or subsequent year, we must add a check to account for this. We do this in the __WeekDate1 variable. We begin by checking whether our __WeekDate variable is blank by using the ISBLANK function. If __WeekDate is blank, then we know that the week does not start or end in the current working year and we need to check the previous or subsequent year. If __WeekDate is not blank, we simply return its value to __WeekDate1.
The RETURN line can be modified to return whatever format is desired, including just returning the raw date from the __WeekDate1 variable.