Better to use DAX calendar table or power bi and M and why?

[jaryszek]

Hello,

I have this kind of code to generate dates:

DateDimension = 
ADDCOLUMNS(
    CALENDAR(DATE(2023, 2, 1), DATE(2025, 2, 1)),
    "DateKey", FORMAT([Date], "YYYYMMDD"),
    "Year", YEAR([Date]),
    "Quarter", "Q" & QUARTER([Date]),
    "QuarterNo", QUARTER([Date]),
    "Month", FORMAT([Date], "mmmm"),
    "MonthNo", MONTH([Date]),
    "MonthShort", FORMAT([Date], "mmm"),
    "WeekNo", WEEKNUM([Date]),
    "WeekDay", FORMAT([Date], "dddd"),
    "WeekDayNo", WEEKDAY([Date], 2),
    "DayOfYear", DATEDIFF(DATE(YEAR([Date]), 1, 1), [Date], DAY) + 1,
    "IsWeekend", IF(WEEKDAY([Date], 2) > 5, TRUE, FALSE),
    "IsHoliday", FALSE, // You can customize this based on your holiday calendar
    "YearMonth", FORMAT([Date], "yyyy-mm"),
    "YearQuarter", FORMAT([Date], "yyyy") & "-Q" & QUARTER([Date]),
    "FiscalYear", "FY" & IF(MONTH([Date]) > 6, YEAR([Date]) + 1, YEAR([Date])),
    "FiscalQuarter", "FQ" & IF(MONTH([Date]) > 6, QUARTER([Date]) - 2, QUARTER([Date]) + 2)
)

This is dax. 

I heard from my boss because he read in some article that this is better approach than writing this in power query M code - it is regarding performance and size. 

It is true? And why if it yes? 

If i will need to read speofic ranges of dates based on specific customer (like read start date, end date) dax will be better or just import them using power query? 

Need expertise here please,

Best wishes,
Jacek

[jaryszek]

Thanks, so calculated tables can be a good solution not having affect on general performance. 
So it is matter of implementation.

Best,
Jacek

[Deku · Accepted solution]

Any DAX calculated table and columns are generated at the very end of a refresh. This data will still be compressed.

For calculated tables, the compression should be the same regardless of if the data is from DAX or powerquery.

In the case of calculated columns, there can be a difference. When loading data, vertipak tries to find a sort order to increase run length encoding and maximize compression. The mean you get smaller tables in memory and faster query execution. Calculated columns are calculated after this order has been determining, meaning it may have sub optimal compression.