This blog is part 2 of an introductory guide on how to use DAX (Data Analysis Expressions). Today will be covering date functions within Power BI Desktop.
This and the worked examples are based on what I learned from Robbin Vernooij! *pause for applause*
Before we start, here is a Cheat Code of date functions to remember for DAX:
- TODAY: This gives the date for today.
- NOW: This gives the date for today with time.
- DATE: This creates a date when given the year, month, and day values.
- YEAR: This gives the year value from a date.
- MONTH: This gives the month values from a date.
- DAY: This gives the day value from a date.
- HOUR: This gives the hour from a time or date/time value.
- MINUTE: This gives the minute from a time or date/time value.
- SECOND: This gives the second from a time or date/time value.
- DATEDIFF: This gives the difference between two dates.
Here is a worked example that shows use cases for these functions: Insert a calculated column that contains the weekday number, make sure to have Monday = 1.
- First, navigate to 'Table Tools' in the dynamic ribbon bar at the top, here you can find the calculation tabs that we will be using.
2. In this task, there are full dates, we want to add a New Column and use DAX code to create just a week day column.
3. Here is an example of DAX you can use:
Hope this was useful! Have a go exploring other DAX syntax for other uses too.