Issue with WEEKDAY returning 7 for monday

[Timour]

Hello everyone,

I am facing an issue with WEEKDAY(). I lookep it up online but couldn't find anything similar.

1 - Case when it works
So, I am writing this question on April 19th which is actually a Sunday.
When I use this 

It actually works and give me 7 as an answer

2 - Case when it doesn't work

But when I try to do it with a column of my table

Colonne 2 = WEEKDAY('POWERBI_DATA'[Ref_Last_Pending_Date_Format];2)

This is what I get :

So you can see on the screeshot above that I get results of 7 for mondays instead of 1.

I have no idea why this is happening. I was just trying to get the average amount of operations per weekday (excluding weekends) so I excluded 6 and 7 but the figures didn't make sense until I noticed this...so now when I exclude 5 and 6 (which are then Saturdays and Sundays...) the figures are correct.

I don't know if this could be part of the issue but just so you know : 

   - the column Ref_Last_Pending_Date_Format is calculated based on the column Last_Pending_Date (text) as follows : DATEVALUE('POWERBI_DATE'[Last_Pending_Date])
   - The date format is DD/MM/YYYY

Thank you in advance for your help and ideas

Tim

[amitchandak · Accepted solution]

@Timour 

1. Is your date of DD/MM/YYYY has been deteted as date?

2. Have you tried using

Colonne 2 = WEEKDAY('POWERBI_DATA'[Ref_Last_Pending_Date_Format].date;2)

3. Create a new date like this and try

new Date  = date(right('POWERBI_DATA'[Ref_Last_Pending_Date_Format],4),mid('POWERBI_DATA'[Ref_Last_Pending_Date_Format],4,2),left('POWERBI_DATA'[Ref_Last_Pending_Date_Format],2))

4. In case all you date are in DD/MM/YYYY format try

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