A Helpful DAX Function for Power BI Date Tables

Data, especially in the time of quarantine, can make you look REALLY good on paper. Or, on the screen I should say-- or in this case in a table.

And if I am being honest, putting all my cards on the table (you're welcome for that pun), I've definitely tried to use data in building a defense when my wife has called me out on something. Unfortunately for me, she knows how a cherry-picked data table can tell many different stories. Nevertheless, I persist!

In this post I discuss:

*DAX function CALENDARAUTO

*why I broke-up with function CALENDAR

*my video demo I created

*how I attempt to use a Date Table to make myself look better to my wife 😁

I’ve really loved learning about DAX functions lately during my deep dive into the world of Power BI.  As you know I have a math teaching background, so it is fascinating to see how I can manipulate simple calculations to use time intelligence, override specific filters, and cross reference other tables. One of the things I came across though the other day while helping out a client is a simple DAX function that I hadn’t given much thought to at the time. That function is CALENDARAUTO.

I had learned in the past that it was really simple to make a date table using DAX just by implementing the function CALENDAR.  With the CALENDAR function you simply put in your start date and end date and voila!,  a 1-column table of all the dates from the beginning to end are created for you. It was a simple enough formula to implement and it was the one I used, until this past week.  Why did I move on from this formula you ask?  Well, it comes down to doing a data refresh.

I was working with a client and everything was going smooth until we refreshed her data and our table visual did not look quite right.  What happened?  Well, when she refreshed her data there were some new data associated with dates that were after our predefined ending date and before our predefined starting date.  It was a pretty easy fix though because we just went in and sorted her table to look for the earliest date and latest date and then updated our CALENDAR function with those dates.  

 You can see the problem, however, if we are going to refresh the data in the future we will have to fix the problem each time.  Well, let me introduce you to our friend CALENDARAUTO to do the heavy lifting for us.

The DAX function CALENDARAUTO will scan through every single table we have loaded into our Power BI model and look for the oldest and most recent date.  When it finds the earliest date it will automatically make January 1^st of that year as our starting point for our date table.  When it finds the most recent date it will automatically make the December 31^st of that year our last date for our date table.  This means we no longer have to do anything manually and the date table will always updated when new data with dates is brought in.

I can’t share her data below so I thought I would put out a real basic video example to illustrate the differences between the two. Check it out below. Again, thanks for following the journey!

To follow my blog, The Algebra Teacher Powers to B.I. click here. 

If you want or need to increase your Power BI skills, Pragmatic Works On-Demand Learning platform is the perfect place to do it. Our 17 Power BI courses will turn you into a Power BI expert by providing in-depth instruction on all of the capabilities that Power BI provides. Click the link below to get started with a FREE trial!