At times it’s necessary to extract or convert a date to the name of a day of the week, or the numeric value for the day of the week. Both techniques can be completed using different DAX functions
Table of Contents
Creating New DAX Columns vs Measures
When converting dates to a weekday name or a weekday number, you will nee to create a New Column rather than a measure. When DAX functions are used to create a New Columns it is evaluated on a row by row bases where as Measures are meant more for DAX aggregation functions, such as SUM or AVERAGE. In this scenario we want to evaluate each individual date within our table, so we will use New Columns to perform the conversions.
Converting Dates to Weekday Names With DAX
To quickly extract the name of the day of the week using DAX, use the FORMAT function. It provides options to return the name of the day of the week in the fully written out name for your locale or the abbreviation.
Using the FORMAT DAX Function
To convert a date to a weekday name in Power BI, create a New Column and use the DAX FORMAT function. The FORMAT function allows you to convert any date column to various formats of the day of the week or display the day of the week as a number.
Because of its versatility the FORMAT DAX function is the preferred method for converting a date to a weekday name.
The syntax is as follows:
Weekday Name = FORMAT('Date'[Date],"dddd")The “dddd” part of the DAX formula is the designation for the type of formatting to return from the Date Column of the Date Table and the results of the formula when applied to a date column are as follows:
Additional formatting options for the weekday name exist to give developers more control over the process.
- ddd – Displays the date as Sun-Sat
- dddd – Displays the date as Sunday – Saturday
Many additional formatting options exist to extract or convert various parts of a date using the same formula which you can read more about here: FORMAT function (DAX) – DAX | Microsoft Learn
Convert Dates to Weekday Numbers with the DAX FORMAT Function
You can use the DAX FORMAT function to convert a date to the number of the day of the week using the following syntax. The “w” string format tells the DAX formula to return the number of the day of the week with day #1 starting on Sunday.
The syntax is:
Weekday Number = FORMAT('Date'[Date],"w")The versatility of the DAX FORMAT function makes it great for these types of conversions but has some limitations in that you cannot define which day to start the week on, it will always default to Sunday. For more control, you can use the WEEKDAY DAX function.
Convert Dates to Weekday Numbers with the DAX WEEKDAY Function
To convert a date to a numbered day of the week, use the DAX WEEKDAY function. The syntax is: WEEKDAY([Date],Return Type) where the Return Type is a number from 1 – 6 that designates which day of the week to begin counting. The default is 1, Sunday with options to start on Monday or with the number zero.
The syntax with no modifications on the start date is:
Weekday Number = WEEKDAY('Date'[Date])The syntax to convert a date to the number of the day of the week while starting the week on Monday is:
Weekday Number = WEEKDAY('Date'[Date],2)And another option exists ,3 that will start the week on Monday but begin counting as day zero. You can learn more about the WEEKDAY function here: WEEKDAY function (DAX) – DAX | Microsoft Learn