How to get day name on Direct Query

[adriancho_BI]

Hello! 
I'm trying to get the day name (monday, sunday, etc.) and I'm using DirectQuery, this limit the formulas I can use. I mean that I can't use WEEKDAY OR WEEKNUM directly. And I would like to create a new column that tells me this field. My date column is datetime.

I already have different "Start of" columns, like: Start of Day, of hour, of Month, of Week

Thank you for the help.

[Anonymous · Accepted solution]

Hi @adriancho_BI ,

@bhanu_gautam Thanks for your concern about this case! I think your answer is pretty close.
After my testing, in fact you only need to use this DAX to create a measure, but it can't be achieved as a column:

Measure = FORMAT(MAX('test'[Resolved Date]), "dddd")

The final output is as below:

The test environment I use is Direct Query connecting to SQL.

And if you just want to know what day of the week the date is, you can directly select the Format here:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @adriancho_BI ,

And if you want to achieve this in a column, maybe you can try to use Power Query:
Here is my sample data:

Use this M function to create a custom column:

Date.DayOfWeek([Resolved Date], Day.Monday) + 1

Then create a conditional column:

And the final output is as below:

Then click "Close & Apply".

Here is the M function of these two steps:

#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Date.DayOfWeek([Resolved Date], Day.Monday) + 1),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = 1 then "Monday" else if [Custom] = 2 then "Tuesday" else if [Custom] = 3 then "Wednesday" else if [Custom] = 4 then "Thursday" else if [Custom] = 5 then "Friday" else if [Custom] = 6 then "Saturday" else if [Custom] = 7 then "Sunday" else null)

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@adriancho_BI , You can try using format and left function 

Day Name =
SWITCH(
TRUE(),
LEFT(FORMAT([Date], "dddd"), 3) = "Mon", "Monday",
LEFT(FORMAT([Date], "dddd"), 3) = "Tue", "Tuesday",
LEFT(FORMAT([Date], "dddd"), 3) = "Wed", "Wednesday",
LEFT(FORMAT([Date], "dddd"), 3) = "Thu", "Thursday",
LEFT(FORMAT([Date], "dddd"), 3) = "Fri", "Friday",
LEFT(FORMAT([Date], "dddd"), 3) = "Sat", "Saturday",
LEFT(FORMAT([Date], "dddd"), 3) = "Sun", "Sunday",
""
)

[adriancho_BI]

thank you for the alternative, but I can't use directly the date column aparently because PBI shows me the error "Can't find name '[date]'". 

[bhanu_gautam]

Use your date column and table name instead of that

[adriancho_BI]

You're right, that was the first thing I did after copy your suggestion /: then the error showed up.

Thanks anyways!