Calendar Table with DAX function

Hi all,

I finished watching the Power BI Super Users Workshop.
I noticed that a Date table was created based on a query in which we have to insert the start date and the end date for our table.
The question I want to ask is:
Why not create a Calendar table in DAX that will read the dates from the oldest and newest records and thus dynamically create a Calendar table if we have to worry about whether the table created in Power Query still meets the date requirements of our fact table?

Regards
Joao

@JoaoMonteiro,

You could use DAX as well to create a Date Table. As a best practice we use Power Query, but you are right in thinking that you could use DAX as well. I would highly recommend using the Power Query version, but the choice is up to you. Here are a couple resources if you are interested in developing a DAX Date Table.

** Attached, is a file from Avi Singh, that he uses to create with DAX**

DAX Date Template.pbit (58.1 KB)

Thanks
Jarrett

2 Likes

HI @JarrettM

Thank you very much for your quick response.

@JoaoMonteiro,

If I can make another suggestion along these lines - stick with @Melissa’s Extended Date Table code, but use the following techniques to make the start and end dates dynamic, as you suggest.

The Extended Date Table gives you tremendously powerful time intelligence capabilities. We did a whole video series on how to utilize these capabilities that you can find here:

  • Brian

@JarrettM

I created the Calendar table like this:

Calendar = CALENDAR(
DATE(
YEAR(MIN(AgentSchedules[Date])),
MONTH(MIN(AgentSchedules[Date])),
1),
DATE(
YEAR(MAX(AgentSchedules[Date])),
MONTH(MAX(AgentSchedules[Date])),
31)
)

Why do you consider it best practice to do it within Power Query?

@JarrettM

Sorry, I only saw the first video, which I loved, but I already got the answer I wanted.
Thank you

Is it better to use Calendar or CalendarAuto functions in DAX, or build the date dimension using Power Query?

This is always the question that I get; where should I build my date dimension? My answer is always one thing: Wherever you feel more comfortable with it! You have to maintain this dimension table going forward, so you have to decide where you are going to build it. there are pros and cons of building it in everywhere, for example;

  • Calendar() and CalendarAuto() functions are very simple to use, and fast way of creating a date table.
  • If you create a date table using Power Query, then you can query live APIs and web services and fetch public holidays and other information live.
  • If you build your date table in the data source, such as the SQL Server database, you can then use it across multiple visualization tools.

@JoaoMonteiro,

If you do go the DAX route, the SQLBI approach that @JarrettM sent works really well for a simple date table.

However, if at some point you need a more full-featured date table but want to stick with DAX, here’s a template for creating the King Kong of DAX date tables:

  • Brian
1 Like

Brian

I have so much to learn that it becomes scary.
As the questions arise and if I have not found the answer in the many workshops that you have available, I will come to you for your precious help.
Thank you so much for the availability.

Joao

@JoaoMonteiro,

The depth, flexibility and power that makes Power BI so fascinating is also what can make it seem incredibly daunting at the start. It’s impossible to master all aspects of it, and even if you could, with the monthly program updates, there is always new stuff to learn.

Here are a few things that I think will help guide you on the journey:

  1. eDNA Learning Map - @sam.mckay has put together a fantastic organized learning plan for how to best utilize the wealth of content on the site.
  1. eDNA Forum - this forum is an absolutely amazing community filled with incredibly talented, friendly people who are eager to help each other. Whenever you get stuck or just have a question you want input on, post it on the forum and you’ll probably get multiple answers/solutions. Here’s a guide to making the most of the forum:
  1. eDNA Data Challenges - this is a new aspect of Enterprise DNA - bi-weekly challenges where you are given a real-world scenario and the associated data, and challenged to create a report that best analyzes and visualizes the data

It’s a phenomenal way to build your skills in the four pillars of Power BI (data modeling, Power Query, DAX and visualization), and learn from your peers. Everyone is welcome and encouraged to participate, regardless of experience level.

  1. Strategies for Improving Your DAX and M Skills - this is a video I’m currently putting together for the Enterprise DNA TV YouTube channel that should post next weekend or so that I think provides some additional helpful tools and strategies for structuring your development that very much complements the Learning Map above.

I hope this is helpful. Good luck and enjoy the journey!

  • Brian
1 Like

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

@JoaoMonteiro,

Here is a new YouTube video that SQL BI just posted on setting up a DAX Date Table.

Thanks
Jarrett