Converts day of week to number from 1 to 7

[vedran]

Hi guys

In (calculated) dimension/calendar table, I have Day(s) of week:

Mon

Tue

Wed

Thu

Frid

Sat

Sun

Mon

Tue..

Which DAX expression I can use to have this values in new column as a number:

1 or 01

2

3

4

5

6

7

1

2...

Thank you

[mfbati]

In Power Query DayOfWeek function returns a number from 0 to 6. If you need a number between 1 and 7 in Power Query, you can use this code:

While the first day of a week is Sunday:

DayNo = Date.DayOfWeek([DateColumn], Day.Monday)+1

While the first day of a week is Monday:

DayNo = Date.DayOfWeek([DateColumn], Day.Tuesday)+1

[vishal097]

Thanks, it is working fine.

[vedran · Accepted solution]

I solved..

WEEKDAY Function (DAX) is for that

https://msdn.microsoft.com/en-us/library/ee634550.aspx?f=255&MSPPError=-2147217396 

DayNo = WEEKDAY(DateKey[Date];2)

---