Power Query or DAX for Calendar table?

[ClaireS]

DAX newbie here. I see that you can create date tables for Power BI either in Power Query or in DAX. To my mind, the DAX method seems much easier (CALENDARAUTO then add extra columns as needed), but I have also seen it done in Power Query - usually either asking for StartDate and EndDate parameters or with some clever function call to extract these from the data. From my learning, I gather that generating tables and columns is best done "upstream", i.e. in Power Query, but as I say this seems more complicated.

What would be considered best practice - and why?

 

[Matt Allington]

There are different approaches and views. I favour PQ
1. all data prep is in the same place
2. You can cut and paste the code and reuse again, 20 secs to create a calendar table.

if you use DAX the way you describe and there are 20 columns, it is a lot of work each time. You can do it in a DAX table function (in PBI, not Power Pivot), but I just prefer PQ for the reason in point 1.

i literally just copy the table I created here, and change the dates.

Build a Reusable Calendar Table with Power Query

In this step by step guide you'll learn how to create a Calendar Table using Power Query and save the code to reuse it later.

exceleratorbi.com.au

 

[ClaireS]

There are different approaches and views. I favour PQ
1. all data prep is in the same place
2. You can cut and paste the code and reuse again, 20 secs to create a calendar table.

if you use DAX the way you describe and there are 20 columns, it is a lot of work each time. You can do it in a DAX table function (in PBI, not Power Pivot), but I just prefer PQ for the reason in point 1.

i literally just copy the table I created here, and change the dates.

Build a Reusable Calendar Table with Power Query

In this step by step guide you'll learn how to create a Calendar Table using Power Query and save the code to reuse it later.

exceleratorbi.com.au

Thank you so much. Yes, I think point 1 is the key here. Point 2 less so, because you can copy and paste DAX just as easily as PQ! I shall certainly work through your excellent article - and maybe make some of the adjustments suggested in the comments.

One interesting aside is the British tax year, which unhelpfully runs from 6th April one year to 5th April the next, so not based on an actual month end. In DAX, with CALENDARAUTO, I think I'd be limited to a fiscal year starting on 1st of the month, but with your method, I can simply adjust the offset days to 270 instead of 184...

Off to have a play now!

 

[Matt Allington]

"Point 2 less so, because you can copy and paste DAX just as easily as PQ", not really, not if you do it the way you described. If you do it with calculated columns, you will have to cut and paste 1 column at a time x the number of columns. 20 columns in total = 19 separate cut and paste events, in order, waiting for each to calc before moving to the next. In PQ, its a single cut and paste.

 

[ClaireS]

"Point 2 less so, because you can copy and paste DAX just as easily as PQ", not really, not if you do it the way you described. If you do it with calculated columns, you will have to cut and paste 1 column at a time x the number of columns. 20 columns in total = 19 separate cut and paste events, in order, waiting for each to calc before moving to the next. In PQ, its a single cut and paste.

Not if you do it all with ADDCOLUMNS around the original CALENDARAUTO . But might be tricky with some of the more difficult transformations - I had a lot of fun generating Tax Months in PQ (6th April year start) and am not sure how I would go about doing that in DAX. All part of the learning curve!