Power Query or DAX for Calendar table?

Matt Allington said:

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

Click to expand...

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!

"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.

Matt Allington said:

"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.

Click to expand...

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!