Let’s look at how to use the CALENDAR and CALENDARAUTO DAX functions in Power BI.
First, let’s create some data. In Power BI Desktop, select Enter Data:
Let’s create a table with 2 dates, 01/01/2014 and 01/01/2018:
Now let’s create a new table from the Modeling tab:
We will call it Dates Table. Start typing CALENDAR to see the 2 options:
CALENDAR returns a table based on Start and End Dates:
Enter a date, in the format DATE(Year, Month, Day):
We will enter a range from 1/1/2016 to 12/31/2017:
Using CALENDAR, we get a table returned between these specific dates:
Until:
The table created has a date hierarchy:
Let’s delete this table and create a new one using CALENDARAUTO:
We will use 12 as the fiscal year end month:
We can see the table has looked at the data in our model and seen the earliest date is 1/1/2014 and started the date table there:
And then ended the table on the last date, 12/31/2018:
Let’s add another table, InvoiceDates, which has an earliest date of 1/6/2010 and latest date of 1/7/2021:
If we go back to the Date table, we see the earliest date has now changed to reflect this, however the start date is 1/1/2010 not 1/6/2010:
And the latest date is 12/31/2021, not 1/7/2021:
Date Table 2 also has the hierarchies:
Let’s look more at how Fiscal Periods work with CALENDARAUTO. Let’s say we only have the table below with the dates 5/1/2014 – 7/1/2016:
If we use CALENDARAUTO() without any quotes, we see it is generating from the start of the earliest year, 1/1/2014:
To the end of the last year:
Let’s change this to CALENDARAUTO(1). This is now assuming the fiscal year starts in February. with the first date 2/1/2014:
And ending in the next January after the last year, in this case 1/31/2017:
CALENDAR(2) will start in March, 2014 up to CALENDAR(4) which will start in May 2014. CALENDAR(5) will then start in 6/1/2013, up to CALENDAR(11) starting in December, 2013, and CALENDAR(12) will start in January, 2014.
From here, we can build out our date tables by adding more columns to it. We can determine which one of these to use (or neither one) based on our data requirements.
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
