Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Which is the best way to create a Calendar Table, using DAX or Power Query? and why?

I have been looking at similar questions, but I havent seen any solid answer, I know how to create a Calendar Table using DAX or Power Query, but I don't really know in what situations should I use one or the other. 

 

Hope you can help me guys!! 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous ideally your solution is to pickup based on the size of the data.

 

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

 

Thanks

View solution in original post

6 REPLIES 6
theov
Advocate II
Advocate II

Hi, in big picture what works better for you, is the best. But I would say DAX, because you will have more control over it and in any case as your project grows you'll need dax functions to expand your calendar with new column etc.

There is one exception though, if your company has a specific calendar in its database, put that as your start point:

This is great video to all you need in calendar with dax also:

https://www.youtube.com/watch?v=Oq5WOmo94_Q

 

Icey
Community Support
Community Support

Hi @Anonymous ,

 

It is hard to say which is better. For many scenarios they are similar. It depends on the your requirements to choose which method. So, it might not be different to use Power Query or DAX for it.

 

However, there is a big difference. Power Query can fetch data from live web APIs. This functionality gives you the power to fetch public holidays live from an API. You cannot do this with DAX! Apart from this big difference, majority of other requirements can be done with both.

 

 

Reference:

Creating Calendar Table in Power BI using DAX Functions - RADACAD;

Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SushainKoul
Helper III
Helper III

Hi @Anonymous 

I would suggest u to first everything from the source but in case u are not able to achieve that then I would suggest u to go for DAX approach as it is the easiest of all as follows:

One of the easiest ways of creating a calendar table is using Calendar() function in DAX. The Calendar function is a very simple function with just two input parameters; start date, and end date.

<table output>=Calendar(<start date>, <end date>)

The output of the Calendar function is a table with one column which includes all dates between the start and end date, with one day at each row.

Hope this answers your query.

mohammedadnant
Impactful Individual
Impactful Individual

Hi @Anonymous 

 

Ideally, best practice is to get everything from the source, if not from Power Query, if not then from DAX,

So, as per your choice, I perfer to do it in Power Query rather than DAX

 

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/c/taik18

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18
Anonymous
Not applicable

@Anonymous ideally your solution is to pickup based on the size of the data.

 

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

 

Thanks

Anonymous
Not applicable

Every answer in this thread is right, however, the impact seems to rely on the size of the data.

The video in the provided link explains this perfectly. 
The use of Power Query or DAX depends on the scenario and functionality you need. 

 

Thanks everyone!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.