Query regarding Date

[Sushain_Koul]

Hello All,

Can anyone pls tell me whats the difference between using date (year and month only) as hierarchy vs year and month as calculated column from calendar table. Left image has date hierarchy field which is showing unnecessary months which are not even present in dataset whereas Right image has two calculated columns Year and Month and its showing only those months which are part of dataset. Thanking u in advance for your help.

[v-linyulu-msft · Accepted solution]

Hi,@Sushain_Koul 

In Power BI, the difference between using dates as hierarchies and using years and months as calculated columns in a calendar table is the way the data is organized and displayed.

1.Firstly you can only use dates as hierarchies if your data column type is Date, Date/Time, etc. Secondly, after you use it, Power BI automatically creates the year, quarter, month, and day hierarchies for you. This is useful for you to work with more complete date data, but it may also show all possible time periods, including those that don't exist in the dataset. As you can see in the following figure, this is true not only in the matrix view, but also in, for example, the slicer view.

You can also turn off the hierarchy here:

2.Please note:

Make sure you have the option to turn on the date hierarchy in your Settings:

Here are the links to the relevant documents：

Auto date/time in Power BI Desktop - Power BI | Microsoft Learn

If the date columns of your two tables have a relationship, the date columns of only one table have a hierarchy:

2.On the other hand, when using year and month as calculated columns, you have more control over what is displayed because you are defining the columns yourself. This means that only the months in the dataset will be displayed in the visualization, as shown in the right image you mentioned, and it is also possible that powerbi recognizes the output of your calculated columns as a text type. If you only need to report on a specific period and don't need the full hierarchy that Power BI provides by default, then this approach will make your visualization more concise and efficient.

3.Here is a link to the relevant documentation which I hope you find helpful:

Auto date/time guidance in Power BI Desktop - Power BI | Microsoft Learn

Create date tables in Power BI Desktop - Power BI | Microsoft Learn

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

Best Regards,

Leroy Lu

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

[Sushain_Koul]

Hi @v-linyulu-msft ,

Thanku for a detailed explanation. I had worked on this yesterday and I found the reason why this is happening. The link you have shared for Auto date/time I had gone through yesterday and it cleared most of my doubts. Again thanking u for your detailed explanation and also sharing additional links for knowledge. It will be very helpful for others just like me.