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

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

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC