Date table advantages

[AndrejZevzikov]

Hi, Guys
I just wondering, that advantages to mark my calendar table as date table?
I see only issues after doing that. I cant use default date hierarchy and drill date from year to year.month in visualization a see information only day to day (it's sometimes very annoying in some charts.). I cant create easily month collum for example [date].[month] that way and so on.
And I guessing maybe date table have some advanteges that I didn't know?

[v-yetao1-msft · Accepted solution]

Hi @AndrejZevzikov 

(1)When you use Date Hierarchy in the visual , the users have to drill down to month from year every time , most of the time, this operation is unnecessary .When the date table is marked as date, the Date hierarchy table is removed for Date within the Date table,this saves space and you can simply create your own date hierarchy to work with .

(2) We can get rid of inaccurate values when we deal with time intelligence functions .

I found some blogs to help you better understand the function .

https://debbiesmspowerbiazureblog.home.blog/2020/07/29/power-bi-why-marking-a-date-table-is-a-good-t...

http://nisalbi.blogspot.com/2018/08/mark-as-date-table-in-power-bi-what-and.html

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

A Date Table is considered a "best practice". Marking the Date Table as such prevents PBI from creating underlying "date tables" for each of the date fields in the model (and therefore bloating the model).

The common practice is to include columns for date aggregations (month, year, quarter etc) in the Date Table to be used in the reports.

[amitchandak]

@AndrejZevzikov , Marking date table, check that there are continuous dates. And it can be used with time intelligence function without fail.

The best practice for time intelligence is to have a table that has been marked as date table.

refer for more details

https://www.sqlbi.com/articles/mark-as-date-table/