Date.DayOfWeek returning the wrong day number?

[Mixednuts]

Similar to many, I create a date dimension table to support various reporting requirements. 

One of the elements I am interested in is whether or not a given date is on a Weekend or is a working Weekday. Using the formula below I calculate a new column with these values in Power Query.

I am not sure why it is treating Saturday as a week day and Monday as a Weekend day. According to my outlook calendar, 7-Aug-2021 is a Saturday

Have I missed a setting somewhere?

This is the version I am using:

BTW: We are using Windows 10 with an English (Australia) language setting......

[VahidDM]

Hi @Mixednuts 

Try to add a custom column with this code:

if Date.DayOfWeek([Date])=5 or Date.DayOfWeek([Date])=6 then "Weekend" else "Weekday"

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

[Mixednuts]

Thanks @VahidDM.

That might be an immediate short term fix to the current instance of this report, however we have used this function in many other reports which have already been published. If the behaviour of the function has changed, all those reports would need to be ammended.

Also, if the function behaviour has changed, why do the constants (eg Day.Saturday) remain as before and not reflect the new order?

I looked more closely at the online documentation  
https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek
and it refers to defaulting to the culture default if not explicitly stated as a firstdayofweek parameter. This is the case in our function calls.

Further reading indicates PowerBI takes the defaults from the regional settings of the device on which the PBIX was originally created:
https://www.sqlbi.com/articles/changing-the-culture-of-a-power-bi-desktop-file/

Following that line, it seems our device language settings were recently changed and the regional default for that language now sets the FirstDayOfthe Week to Monday.

So I now know the source of the problem and its scale I have only to 'fix' the regional settings for those reports developed under the new regional settings.

I am still not sure what impact this will have when published online given the default language of the host servers and the lanuage settings in the user's browsers. 

I will test it further unless someone can point me to some definitive documentation?

Thanks!