Custom WEEKDAY based on users selection / preference

[Anonymous]

Hi all,

I want to give my user a choice which day of the week the week starts with. I have a normal calendar table where all my dates are being displayed as well as the day name, month, quarter, year etcetera. This table is been created in Power Query and then loads into the datamodel. It is connected with several tables who are using the calendar table for displaying data on a timebases. See the full code below in the attached image.

But the problem is that I have set a hardcoded week start day in Power Query. This is ba sed on the settings that I determine. So now every week starts on a Monday and ends on a Sunday. The code to determine the week start day is as followed:

InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date], 1) + 1, type number),

I want my user to give an option which day a week has to start with so it could choose between any day of the week. I prefer to solve this with a slicer or filter which will be available in the report. But if there are also other options possible I would like to hear this too. 

I tried to find some solutions on the internet but have only managed to find one example which asked the same as what I want to have: User to Select Week Start Day 

I tried to get this working but it did not work for me because this solution requires a new table. And I would like to

display all my data based on my main Calandar table. 

I have also tried to use a calculated column with the weekday function of DAX and to have a dynamic number as <return_type>. But unfortunately a calculated column does not support measures into this. 

If you need more information please let me know.

[Greg_Deckler]

@Anonymous Right, you would have to use a measure to perform this function. Hard to be more specific without knowing more about your data model. One method might be to essentially recreate a simple calendar inside the measure using CALENDAR for example, use ADDCOLUMNS to add in your desired columns for week information based on the start of week selected by the user. You would then figure out the dates and then filter your base calendar table according to those dates using something like CALCULATE or CALCULATETABLE. Again, hard to be specific about without a solid use case.

[Anonymous]

@Greg_Deckler Sorry for my late response, thank you for your reply.

What specific information do you need more from me other than the code, which I use to create my date table with Power Query, I send in my first post.