A Date with Power BI - Part 1

Are you creating your first report in Power BI? Or are you a bit more experienced, but finding that things are not working as you expected? Most business data contain dates. That means that most data analysis involves handling dates in both visualizations and calculations.

Getting to know how Power BI handles dates is well worthwhile.

For example, sales data might include the date that something was sold, plus when it was despatched, and maybe when payment was received. Training data will include the date someone books a training course, as well as the date they attend.

The passing of time and the events that happen in time are a fundamental building block to the way we analyse things.

Below is a simplified sales data table imported into Power BI. Count of OrderID provides the volume of sales, and it is analysed by date. To create a line chart:

• From the Fields panel, drag OrderID to the Values area on the Visualizations panel. Check the aggregation is Count.
• In the Fields panel, expand the date hierarchy
• From the Fields panel, drag Month to the Axis area on the Visualizations panel. 

The line chart shows the volume of sales by month. 

Alternatively, delete Month from the Axis area and drag Date hierarchy in its place, you can see that now a drill down symbol has been added to your chart. Use it to drill up and down the date hierarchy, such as from Year to Quarter, from Quarter to Month, and from Month to day level.

But what if you do not want a date hierarchy? What if you want the date that was in your original data? Right click on the date filed to toggle between date hierarchy and date.

But let us just stop a moment. When you imported your data, did it include a date hierarchy? Mine just had a simple date, with no hierarchy. So where did it come from? At this stage you may say “I’m happy with my report, I don’t care where it came from!” Which is fair enough, but let me ask you some questions:

• Does your company’s financial months and quarters align with those provided by Power BI?
• Do you ever need to analyse data by week?
• Do you use a completely different time period to analyse data, such as a sales period?
• If you drag “Month” to a date slicer, which month is it referring to?
• What if your data had included two dates, and not just one? How should that be handled?

These are not trick questions, honestly! But they are issues that emerge once you start building real reports for real decision-makers, and once your data starts to become more complicated.

We did not do anything to the date field to create a date hierarchy. So how did Power BI separate the month, quarter, and year from the date? The answer is a clue to the data model we need, rather than the data model we have.

Spoiler alert! There is a difference between what you see, and what is actually happening.

What you see is Power BI adding a date hierarchy, presumably because it understands dates. What is actually happening is that Power BI has created a completely new and hidden table.

To check that there really is a table, use Dax Studio to have a look. Below is a screen picture of the Power BI generated date table with the new fields: Day, Month, MonthNo, Quarter, QuarterNo, and Year.

There are a few important issues arising from this:

• To create a chart with increments other than days, Power BI created a new, hidden table.
• The new table had separate fields for day, month, quarter and year. Plus, month number and year number.
• The table is hidden, and you cannot change it.

The functionality is provided by a setting in Options, under Time intelligence called “Auto date/time for new files”. When Power BI is installed it is Selected by default. So if you don’t want this functionality go to:

• File
• Options & Settings
• Options
• Time Intelligence
• Deselect Auto date/time for new files

Next time I will look at how to create your own date table using DAX. This is a more flexible approach, and answers at least some of the questions raised earlier.

In the meantime, leave a comment or ask a question in the comments section. Or if you want some immediate help with your Power BI development, get in touch.