Expressing Date and Time Values is a corner stone of most reports as users want to see different trends and values for a certain time period. Working with dates and time in Power BI isn’t always that straight forward and can take up a lot of time if you are not comfortable working with dates and times. This becomes more complex with expressions in DAX or M-Language.
When working with Date and Time values in DAX you often need to make use of functions to complete the Date and Time expression. This is no longer necessary with the new Power BI release. We will take you through an example today of how working with Date and Time in DAX is much more simpler. For more introductions to Date and Time in Power BI, please follow this link.
Expressing Date and Time Values
Working with date and time in DAX require the use of functions. Let’s take you through an example and of what we are used to now and then what the improvement in Power BI is.
We are going to filter our online sales from a minute past midnight on the 1st of July 2021 to a minute before midnight on the 31st of August 2021. We are going to use the FILTER function. In order to filter by Date and the Time we will have to make use of the DATE and TIME DAX functions.
[OrderDate] > (DATE(2021,7,1) + TIME(0,0,01)) &&
[OrderDate] < (DATE(2021,8,31) + TIME(11,59,59))
Date and Time without Functions
Now let’s test the ease of specifying Date and Time directly in the DAX function rather than using functions. We can either specify just a date or a date and time value.
Let’s look at Date Format first:
Date format: dt”YYYY-MM-DD”
If we are going to enter, dt”2021-06-31″ it is the same as June,31, 2021.
Now let’s look at the Date Time Format:
DateTime format: dt”YYYY-MM-DDThh:mm:ss” or it can also be written as dt”YYYY-MM-DD hh:mm:ss”
Let’s test this as another example, dt”2021-06-31T14:30:00″ and dt”2021-06-31 14:30:00″. This will be the same as half past two on June,31, 2021.
[OrderDate] > dt”2021-07-1T00:01:00″ && [OrderDate] < dt”2021-08-31T11:59:59″
Below we can see the difference by applying the above function in order to filter by date and time.
That is it for today from us. As you can see it is a much more concise way of writing Date and Time in DAX. We hope that this will expand your knowledge on Power BI. Go and have a look at our other Blog Posts for some great Power BI content.