Power BI – Today in DAX and M Code

By: David Rohlfs

Introduction

Have you ever been working with dates in DAX or M Code and realized that you weren’t sure how to reference today’s date as a dynamic date? This seems to be a common issue among newer Microsoft Power BI users because of the differences in languages between DAX and M Code.

In this blog we will go over what the functions are for today’s date inside of DAX and M Code. Because the functions are less complex, we will not be going through a walkthrough implementing each of the functions. I will be screenshotting the code so that you can copy it into your Measure, Calculated Column, or Custom Column if you would like to try it out on your own.

When to use it

Referencing today’s date is vital for figuring out anything relative to date or time in Power BI. Whether you should use DAX or M Code depends on your situation, but either language has its own functions for today’s date and time.

One thing to note about each function in DAX or M Code is that they will update on the desktop report whenever you open or refresh the report, but the functions will only update in the Power BI Service as long as the semantic model is being refreshed. So, unless you have your semantic model refreshed every day, your Today on your Power BI published or service reports could be out of synchronization with today’s actual date.

If you have done today’s Date and Time in Microsoft Excel, the DAX is very similar. There is the Today () function for just the date, and the Now () function for the date and time. Both of these are simple in that today can be used as a date field in any parent function and Now can be used as a date time field in any parent function. Here is a screenshot of two measures that have a card with their measure below it. The left is the Now function, and the right is the Today function.

Let’s look at how you would reference today’s date and time inside of Power Query. Unlike the DAX code, today’s date will always be accompanied by the current time. What you can do is filter out the time portion of the result so that you are left with only today’s date. There are two functions inside of M Code that can be used to obtain the current date and time. These are DateTime.LocalNow and DateTime.FixedLocalNow. The difference between the two is that the fixed local now will set the time to the developer’s time zone while the local now will set the time to the end users respective time zone. Here are the screenshots for each Custom Column and result.

Let me show you how to only get the date from these columns. All you need to do is change the data type from Date Time to Date.

If you are using one of the M Code functions in a formula and want only the date, you will need to use a Date.From function outside of the DateTime.LocalNow function.

Conclusion

While getting today’s date is simple once you know it, there are challenges to learning the process and differences among the different formulas. Hopefully this blog helped explain some of those differences and gave helpful insight to how you can use the functions in the future. If you enjoyed this blog there are a few other EPM Strategy related blogs below.

 

Links Related to This Blog:

Why Create a Date Table in Power BI

Adding a Custom Column

Conditional Column

Date Time Format

Comments