Date Table

[koorosh]

Hi Experts, when I create a Date table by CALENDARAUTO() DAX and mark it as a date table, I got the image1 chart. But if I do not make it as a date table, I got the image2 chart. Why the difference and so, what is the use of Mark as Date Table?

Regards

[v-janeyg-msft · Accepted solution]

Hi, @koorosh 

'Mark as a date table' most of the time is to use the time intelligence functions. In fact, it is not suitable for newcomers if you need the date hierarchy. As you can see, you can’t use a default hierarchical structure to display data in visual. 

If you want to create your own date table and define your own date hierarchy, you can mark as a date table.

Set and use date tables in Power BI Desktop - Power BI | Microsoft Docs

Mark as Date table - SQLBI

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[parry2k]

@koorosh not sure what answer you are expecting?

[parry2k]

@koorosh it is best practice to common date dimension tables, few reasons:

- it reduces the size of the model rather than having hidden date tables for each date column in your model

- a common dimension to filter data in multiple fact tables.

- all above, ability to use DAX Time intelligence function for more common time-based analysis Time intelligence functions (DAX) - DAX | Microsoft Docs

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[koorosh]

Frankly, I have not been answered, where is Mark as Date Table useful while we got different result? Check the attached file please when he applies Mark as Date Table? Could we get YTD column without Mark as Date Table?

Regards 

https://drive.google.com/file/d/10jDGmSawwh1H8yo-R2pje5Fh4XFtgnoK/view?usp=sharing

[parry2k]

@koorosh apart from @ebeery said, if you are using date column on the x-axis (in image 1) and the type of x-axis is set to continuous (on format pane), Power BI decided what value it will show on the x-axis, currently, it is showing year, if you filter on shorter date range, it might show month or date on the x-axis and you cannot control it. Again this is only applicable if you are using date type column on the x-axis and type is set to continuous.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ebeery]

@koorosh in Image 2, when you didn't mark as date table, Power BI automatically created a date hierarchy for your date column.

That is what this setting in Options>Global>Data Load does:

Typically it is not recommended that this approach be used.  Rather, it is best practice to build a date table that has the month, year, quarter, etc. columns that you need.

[koorosh]

Thanks ebeery, So when using Mark as Date Table would be useful? Or where it would be useful since you wrote " it is best practice to build a date table that has the month, year, quarter, etc. columns that you need".

Regards