Forum Discussion
Newbie Question - Date Table in Power Bi, DAX or M ?
I recommend M/Power Query. It is recognized as imported data by DAX and has good compression. It can be fully dynamic as well, adding years as your data increases over time, and removing earlier years if you don't want to keep history forever. For example, last 5 years or whatever. See my blog on how to do this.
Creating a Dynamic Date Table in Power Query
If you use DAX, I recommened a calculated table, not a table with calculated columns. The latter will not perform as well.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
I recommend M/Power Query. It is recognized as imported data by DAX and has good compression. It can be fully dynamic as well, adding years as your data increases over time, and removing earlier years if you don't want to keep history forever. For example, last 5 years or whatever. See my blog on how to do this.
Creating a Dynamic Date Table in Power Query
If you use DAX, I recommened a calculated table, not a table with calculated columns. The latter will not perform as well.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Hi Edhans
Many many thanks for you fast answer!