The date column can't have gaps in dates

[Anonymous]

Hi! I'm trying to use a date column as an axis on my chart but I'm having some issues doing it (Figure 1). First, I noticed that I was not able to drill down this date column into Year/Quarter/Month/Day (like Figure 2) because this column was being use as a relationship key to other tables (figure 3). When I remove this relationship, I'm able to see Figure 2 again. The problem is that I cant do that, since it is connecting other tables that I need. I've read on the Community that I have to set up this date column as a table and mark it as a date table to be able to use the "date hierarchy". The problem is that since the company does not open every single day, there are missing dates on my table, therefore I'm not able to set it as a date table (figure 4) and to use the date hierarchy (figure 5). How can I solve this? The main problem here is not being able to put a simple column and see its drill down without removing the relationship that I made to other columns. Thank you in advance!Figure 2Figure 5Figure 4Figure 3Figure 1

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @Anonymous ,

We can create a calculated table contain unique and continuous time using CALENDAR function:

Table 2 =
CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2010, 1, 1 ) )

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adambhappy]

The table you mark as a date table should be a separate table, not the table where you have the transaction data.

Please take a look at: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/