Date.DayOfWeek returning wrong days

[SusuYes]

Hello everyone, 

I am a new PowerBI user. I have been tracking well with learning it and it is immensly fun! 

However, I noticed a mistake and I can't figure out what would be the cause.

I'm working with timesheet data and I want to calculate the expected salaries of the employees. Employees get paid by hour and have two rates [Reg Hours] and [Wkd Hours]. I am trying to identify the days of the week/weekend to be able to calculate the expected salaries. 

I used the Date.DayOfWeek to get the day of the week and based on that I was going to write a measure to calculate the salary however the function is returning wrong details. 

If you notice the first two rows in my table, they have different days however in the 'Week day' and 'Days of Week' coloumns, they are both the same number which indicates that they are on the same day, which is not correct. 

Note: difference between 'Week day' and 'Days of Week' is the first day of the week. 

Has anyone expereicned something similar? or does anyone have any suggestions I could try?

**UPDATE 26/10**

I think I figured out why is it not changing. Currently PowerBI is reading the data format as dd-mm-yyyy even though it is mm-dd-yyyy.

I don't understand why PowerBI decided to read the format wrong even though the data was properly formatted and the settings of Excel and PowerBI are correctly set up. 

[amitchandak · Accepted solution]

@SusuYes , Power bi take date format from your system setting. and sometime it not understand other format.

You can convert either way

DD/MM/YYYY to MM/DD/YYYY
date(year( right(DD__MM__YY[date],4)), month(mid(DD__MM__YY[date],4,2)) ,day(left(DD__MM__YY[date],2)))
mid(DD__MM__YY[Version_Id],4,2) &"/"& left(DD__MM__YY[Version_Id],2) & "/" & right(DD__MM__YY[Version_Id],4)

MM/DD/YYYY to DD/MM/YYYY
date(year( right(DD__MM__YY[date],4)), month(left(DD__MM__YY[date],2)),day(mid(DD__MM__YY[Version_Id],4,2)) )

Power query you use #date and these functions Text.Start, Text.Middle, Text.End: (power query take index from 0 )

Power Query - Text.Start, Text.Middle, Text.End: https://www.youtube.com/watch?v=vky4wPqm0O0

Also check

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

[SusuYes]

Thanks for your reply! This is very helpful.

I'm just wondering, when you say, PowerBI will take the 'System' settings. Are those the PowerBI Desktop App settings, or my PC settings? I will update it to be the format I am always following.