Date Hierarchy showing all dates of the year

[Arkymes]

Hi.

I'm having a issue with the Date Hierarchy of power BI.

I have a Table with all the dates between May/2019 and January/2020r, but for some reason, when i create a chart using it, it keeps showing dates that are not on the table, it shows all de days from january/2019 to dezember/2020.

I tried to filter it, but the chart will not change no metter what date i filter.

This only happen when the Date is in Hierachy, if i remove it then it only show the dates on the Table, the problem is that i need the Hierachy for Drilling down.

Can anyone help me with this?

[v-kelly-msft]

Hi @Arkymes ，

Create a new table using a dax expression as below:

table = filter(table,[date]>=date(2019,5,1)&&[date]<date(2020,1,31))

Here ,in the above expression,' table' is the date table which has hierarchy.

This is a way to help you exclude the dates you dont need.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AilleryO]

Hi Arkimes,

Some questions to be sure about your model :

1/ You have a date Table according to your message ?

1b/ Is your date Table mark as a date Table ? (I think no, because it should remove the date hierachy)

2/ Is your date Table in relationship with your table of facts ?

3/ What kind of relationship ? Cardinality ?

If you have a good date Table, and a good relationship, it should work straight forward.

In your formula refer to the date of your Calendar table, not the one in your fact table.

Let us know

Olivier

[Arkymes]

Hi Oliver thanks for the Reply.

Answering your questions

1 - Yes i have a date table, it is the one that is giving me truble.

1b. Do you meant the type is set as Date? if so yes it is.

2 - No it is not in any relationship 

Look at the print of the problem:

As you can see it is showing dates since January, but my table only have values from May and beyond.
I just want it to show the dates that are on my table.

Thanks again for your help.

[AilleryO]

Hi,

The relationship might resolve the problem.

In the view 'relationship' (model) draw your date field from your date Table to the date of your fact table to create the relation.

Another solution could be to have as start date for your date Table based on a MIN([YourDate]) function to get the first date of your "facts", and a MAX([YourDate]) function to calculate the end date. So your calendar table will be on the same period of time as your fact.

Let us know...

[Arkymes]

Hi,

I tried to use the relationship but still it didnt work, i need to make a line graph that shows the dates in the calendar table and the values from a second table, but i need it to show dates with no value.
The proble is if i do it the graph shows all dates that are not on my calendar table.

My calendar table is as follow:

It start on July, but i dont know why, it shows on the filter visualization dates starting from MAY, as follow:

When i place it in the dashboard it shows all dates ( as shown on the last print).

If i use a line graph the ways i said i want it shows like this:

If i filter down the dates it only filter the values, but not the axis:

My calendar table have the correct values, but i dont know why it keeps showing on the dashboard dates that are not on it.

[AilleryO · Accepted solution]

Hi,

Sorry we misunderstood each other when I was saying (point 1b) your table as a Table Date, it means that you must "declare" your table as a date table.

See here for screenshots :

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

Let us know if it works

I wonder as well if your date table is really one... A date table should be a table with only dates inside (all dates and all of them being unique) and their declination (year Month, Quarter, weeknum ...), but on your screen copy i can see values in your date table ?

[Arkymes]

Hi.

Thanks for the answer.

I did as you said and it helped, the dates that were not on my table are not showing anymore.

But the problem is, i need to compilate the values from each month, and with date table "declared" the chards show values for each individual day, without date hierarchy i cant drill up to the month. Is there any way to do it?

About the Values that were on the calendar, it was me trying to fix the problem messing around with it, nothing important i removed the columns afterwards.

Thanks again for your help.

[AilleryO]

Hi,

Good to know it helped. Do not hesitate to marl it as solved.

For the display of your dates, you can choose any columns of your date table for your visual.

So if you want to use the full date use the field date, if you month to aggregate by month, use MonthName column from your date table.

Have a nice day

[Arkymes]

Isnt there a way to segregate by month/year?

If i use the month on the calendar it will compilate january 2020 with january 2019 will it not?

And another question, when i use the month colunm it mixes the months and display then by alphabetic order instead of cronological how do i fix that?

Thanks again for your wonderfull help!

[AilleryO]

Hi,

You're welcome 🙂

For your sorting problem, very symple. In Tab View, select your column MontName, then and the Modeling tab use button Sort by Column, to select MontNum. (Do the same with all columns of text that needs to be sorted by their numeric counterpart).

If you want to use Year Month, use the column from your calendar table. If you do not have one, create it with a DAX function :

Calculated Column --> YearMonth = YEAR(Date)&"-"&MONTH(Date) to get something like 2020-02

All the best for your reports

[Arkymes]

It worked

Thanks everyone for your time and for your great help.