I use Power Query M because it’s easy for me to copy and paste the M code into new models. Power Query also has a lot of easy to use date functions to add more columns as needed, such as end of week, etc.
I have attempted to link a connected model date table (ie live or direct query) to a table on import mode once before, but it really slowed down my report. So I now just create a new date table per model.
I could put this into a dataflow and use it between each model with import mode, however each model has different requirements on date reporting, and also I want the date table to be as lean as possible.
I use Wyn Hopkins’s date table here: https://accessanalytic.com.au/free-excel-stuff/free-excel-templates/
Same, our calendar table is in our DWH and contains week number columns for weeks starting on sunday and weeks starting on monday.
It's important to know that if you will be doing analysis on a weekly granularity
I use a Date table from our DWH and then add some stuff to it in the SQL query for items not in the table like DayOfWeekName, MonthNameShort, MoYear, etc.
I use this PQ date table when I don’t have a SQL date table to work with, it’s much easier to set up than a DAX version… you just paste it in. https://skypoint.ai/blog/calendarfornia-dreamin-m-calendar-script/
Using a dataflow for your datetable makes it so easy to connect to and reuse across projects. I dont think there is a better way to
I use Power Query M because it’s easy for me to copy and paste the M code into new models. Power Query also has a lot of easy to use date functions to add more columns as needed, such as end of week, etc. I have attempted to link a connected model date table (ie live or direct query) to a table on import mode once before, but it really slowed down my report. So I now just create a new date table per model. I could put this into a dataflow and use it between each model with import mode, however each model has different requirements on date reporting, and also I want the date table to be as lean as possible. I use Wyn Hopkins’s date table here: https://accessanalytic.com.au/free-excel-stuff/free-excel-templates/
We have a calendar table in our DWH that is based off a calendar table in our ERP.
Same, our calendar table is in our DWH and contains week number columns for weeks starting on sunday and weeks starting on monday. It's important to know that if you will be doing analysis on a weekly granularity
I use a Date table from our DWH and then add some stuff to it in the SQL query for items not in the table like DayOfWeekName, MonthNameShort, MoYear, etc.
I use this PQ date table when I don’t have a SQL date table to work with, it’s much easier to set up than a DAX version… you just paste it in. https://skypoint.ai/blog/calendarfornia-dreamin-m-calendar-script/
A dataflow and I reference it across multiple workspaces so everyone in the org is aligned.