BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Posted by Andy Brown on 24 November 2021
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using the CalendarAuto function effectively in Power BI
So this is a bit of a mea culpa. I've been telling people for years that the DAX CalendarAuto function is useless, and Sam has now persuaded me that I might have been dismissing it prematurely!
A little revision: creating a basic calendar table using CalendarAuto
So you can create a new table in Power BI Desktop like this:
Click on this icon on the Modeling tab of Power BI Desktop to create a new table.
Here's the syntax of the CalendarAuto function:
This function automatically generates a column of dates based on the lifespan of the tables in your data model.
So if your financial year ends on 31st March, you'll type in something like:
Perhaps I could have used a shorter table name ...
To give:
The last date for any table in my data model is actually 21st July 2021, but Power BI makes sure that it generates a calendar consisting of whole financial years.
Adding other columns
So the problem with the formula above is that it only gives one column, and we may want to aggregate data by year, month, quarter, etc. To get round this, change the formula to this, for example:
Shiny new calendar table = ADDCOLUMNS(
// take the standard calendar - this is what we'll add columns to
CALENDARAUTO(3),
// start with the year
"Calendar Year", YEAR([Date]),
// the quarter
"Calendar Quarter", "Qtr " & QUARTER([Date]) ,
// the calendar month number
"Calendar Month", MONTH([Date]),
// the calendar month name
"Calendar Month Name", FORMAT([Date], "mmmm"),
// the day of the month
"Calendar Day", DAY([Date]),
// the day number within the week
"Calendar Weekday", WEEKDAY([Date],2),
// the name of the day
"Calendar weekday name", FORMAT([Date], "dddd"),
// the financial year
"Financial Year", IF(
[Date] >= DATE(Year([Date]), 4, 1),
Year([Date]) & "/" & RIGHT(Year([Date]) +1,2),
Year([Date])-1 &"/" & RIGHT(Year([Date]),2)
),
// financial quarter
"Financial Quarter", SWITCH(
TRUE(),
MONTH([Date]) IN {4,5,6},"Qtr 1",
MONTH([Date]) IN {7,8,9},"Qtr 2",
MONTH([Date]) IN {10,11,12},"Qtr 3",
"Qtr 4"
),
// financial month within quarter
"Financial Month" , IF(
MONTH([Date]) >= 4,
MONTH([Date]) - 3,
MONTH([Date]) + 9
),
// name of financial month
"Financial Month Name", FORMAT([Date], "mmmm")
)
Here's the start of what this would give:
Part of the table generated.
Pros and cons
This approach (as distinct from creating a separate calendar table and linking to that) has one big advantage and one big disadvantage:
| What | Details |
|---|---|
| Advantage | The calendar table will always contain the right range of dates (so if you are frequently adding rows to date-based tables, you won't need to worry about whether your calendar will extend far enough into the future or not). |
| Disadvantage | You're not sharing your table with colleagues (so if your manager decides that they want to report by the Mayan long calendar, for example, you'll have to add this separately into each Power BI report that you've created). |
On balance I still think it's more sensible to have a single calendar table which can be maintained and updated centrally, but I recognise that the approach above could make sense for some users.