Power BI – Working with Date – Part II

In the previous post (Working with Date – Part I), I wrote about calculated column for month and year and in this post I will explain how to work with day and week formats.

As prerequisites, your Power BI model needs to have a Date table, which will be used to create a couple of calculated column.

Week # = WEEKNUM(‘Date'[Date])

This function returns the week as a number, such as, 1 for first week of the year to 53 for last week of the year.

Day = DAY(‘Date'[Date])

Day = FORMAT(‘Date'[Date], “D”)

These functions return Days of the Month. Example. 1st to 31st (for January) or 1st to 30th (for April)

Day = FORMAT(‘Date'[Date], “DD”)

This function returns Days of the Month with a leading zero. Example. 01st to 31st (for January) or 01st to 30th (for April).

ShortDayName = FORMAT(‘Date'[Date], “DDD”)

This function returns Day in a short name format.

DayName = FORMAT(‘Date'[Date], “DDDD”)

This function returns Day as a full name.

You can Sort by Column “Weekday” (Please see Weekday column below)

Weekday = WEEKDAY(‘Date'[Date])

Weekday = WEEKDAY(‘Date'[Date],1)

Both of the DAX codes above return the week day number, from 1 to 7 (Sunday=1 through Saturday=7)

Weekday = WEEKDAY(‘Date'[Date],2)

This function returns the week day number, from 1 to 7 (Monday=1 through Sunday=7)

Weekday = WEEKDAY(‘Date'[Date],3)

This function returns the week day number, from 0 to 6 (Monday=0 through Sunday=6)

Summary – Quick Tips & Results