Incorrect year in date hierarchy

[bo_afk]

Hi,

I have a date column in my data with the following format dd/mm/yyyy. However, in my date hierarchy, which was automatically created in Power BI, the years it's returning is incorrect.

It's showing years from 1901 to 2020, whereas in the data, I only have years 2017 to 2020. Anyone have an idea why it's appearing like this?

Thanks

afk

[Anonymous]

it's a very broad definition of the issue, but I would first check date formats. Are they coherent with your data source? Where are you getting data from?

[bo_afk]

They're set up with data type as "date", with the format dd/MM/yyyy.

Data is coming from a table in SQL server. But when I bring this data into Power BI, and it creates the date hierarchy, the Years don't correspond correctly to the date.

[vanessafvg]

1901 is often a year used when the date value is unknown.   

what table are you bringing the year from, have you done a mix and max on that table?

i would do more digging in the data.

[bo_afk]

Hi @vanessafvg , @Anonymous. The data comes out of SQL server as date and appears as below in Power BI,

The hierarchy is created as this:

But when I filter for year, it shows the following:

[Anonymous]

Well, in a case like this I would FIRST look at the original dataset. Does it REALLY NOT contain any day from 1901 or 1902?
Does it contains integers which might be interpreted by PBI as dates?

With this poor information, I can't be of more help

[bo_afk]

Hi @Anonymous , @vanessafvg 

The original dataset does contain 1901, but that is the only valid "incorrect" year. The other years are 2019 and 2020.

In SQL server the field has data type date with the following format

Additionally, if I create a year column based on this date, it returns the correct years.

It is the date hierarchy which seems to return incorrect years

[vanessafvg]

@bo_afk  i would think the date hiearchy which i believe is actually a virtual table will probably pad the gaps between the  dates, if you want more control over the date, create your own date table and your own hiearchy from the date table, i think that might give you more control

[bo_afk · Accepted solution]

Hi @vanessafvg , you're right. It looks like the date hierarchy is just adding in those extra years in the slicer even though the data doesn't actually exist.

When I filter for the incorrect years, it doesn't return any data.

[Anonymous]

@vanessafvg  is correct BUT if you create a date table it MUST have continous dates. 

[vanessafvg]

@bo_afk  any chance you can share your date table?