This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

A little revision: creating a basic calendar table using CalendarAuto
Adding other columns
Pros and cons

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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using the CalendarAuto function effectively in Power BI

A little revision: creating a basic calendar table using CalendarAuto

Adding other columns

Pros and cons

Head office

London

Manchester