Browse 531 attributed reviews, viewable separately for our classroom and online training
Using the CalendarAuto function effectively in Power BI
This blog shows how to generate a table which includes not only all of the dates in your data model, but also a range of aggregator columns such as financial year and month name for reporting purposes.

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:

CalendarAuto table

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:

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:

New table formula

Perhaps I could have used a shorter table name ...

To give:

Final table

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:

The final table

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.

This blog has 0 threads Add post