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.

Week #_18.06.17

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_1_18.06.17

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).

Day_01_18.06.17

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

This function returns Day in a short name format.

Short Day Name_18.06.17

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

This function returns Day as a full name.

Day Name_18.06.17

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

Sort by Weekday_18.06.17

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_1_PBI_18.06.17

Weekday_1_18.06.17

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

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

Weekday_2_PBI_18.06.17

Weekday_2_18.06.17

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

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

Weekday_3_PBI_18.06.17

Weekday_3_18.06.17

Summary – Quick Tips & Results

Quick Tips & Results_1_18.06.17

Quick Tips & Results_2_18.06.17

Quick Tips & Results_3_18.06.17

Quick Tips & Results_4_18.06.17

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: