As with most data analysis tasks, working with time can be the most challenging detail to get correct. Power BI has built-in functionality that will display a drillable hierarchy of year, quarter, month and days when a column, formatted as a date, is dropped onto a visualization. This feature can be handy in the proper visualization as illustrated in the example below.
If you are creating a table and drag a field, formatted as a date, you do not get a single date but the hierarchy as pictured below. This creates a built-in way to drill down through the year on various graphs and tables. On a visualization, this allows you to provide different views of your data over time. You can also remove a level from the list by selecting the ‘X’ next to the level depending on your needs.
However, this is the behavior is the default. If you want to show the actual date field, you have to click on the down arrow next to the date field as shown below. This allows changing the selection from the Date Hierarchy to the field that contains your Date Values.
The two charts below illustrate the two different options. The top chart, using Date Hierarchy, allows you do use the visualization’s drill down functionality, and the bottom graph, showing Date Value, shows the values as they appear in the data table. With 3 years selected, the bottom graph shows all the months over those years where as the top graph, using the hierarchy totals all the years into each month.
You need to add a filter or slicer to make sure that you are looking at the data for only one year and design your visualization to make sure your end user does not make a mistake or get lost in the data.
The built-in date hierarchy feature allows the adding time drill functionality, you just need to be careful you are showing and selecting the correct values in your visuals. The final version is shown below with the months showing only the totals for the selected year. The slicer is set so only one value at a time can be selected.
