Skip to main content

4 reasons why your time intelligence might not be working in Power BI

I recently blogged some instructions detailing the process to create a Date table using DAX in Power BI which is one way to create the foundation that enables the time intelligence functionality of PowerBI.

If any of you have tried to use a date table you'll probably be aware of a couple of 'nuances' that can result in an incorrect result when you begin to visualise the data. I've described the 4 issues I've often had to rectify when working with time series data, hopefully they'll help you with similar issues I've incurred and found solutions to.

Lets assume that you've created the date table and imported the sales data (more detailed explanation and link to files below). You then try and make a bar chart showing the number of sales by date and you end up with the following:

I think most people who've used PowerBI have seen something similar, all of the columns are the same height and are of a magnitude that immediately feels incorrect. If you see this and you think there is an issue in the data or the data model, I'd suggest the fault is associated with one of the following reasons...

Reason No.1 - There is no relationship between the two tables
The first mistake is that there is no relationship between the date table and the date in the sales data. To fix this you'll need to access the data model and drag the date from one table to the other, like this
 
Reason No. 2 - Data is not of Date Format
For the time intelligence to work, the format of the column needs to be Date. Some times when importing information PowerBI sets the data type to text... To change it back, click the drop down next to 'data type' and select 'Date' then click 'yes' to the following alert.

Reason No. 3 - Date is actually date and time
For the time intelligence to work, both columns need to include the date only and not the time. As you may have seen from the image above, the date in the sales table includes the time of sale. To modify the column, one way to do this is to transform the data in the query to Date.


Reason No. 4 - Unit format is incorrect
Just as the format of the date columns is critical, so to is the format of the columns of the data you wish to aggregate. In this example, the Number sold and Value sold columns were incorrectly formatted as text. The steps required to fix this are similar to the date format outlined earlier, but this time the format is changed to whole or decimal number.

Assuming there are no other issues, your file should look something like the following. The X axis isn't as aesthetically pleasing as it could be but the data displayed by day is now correct.


Information and links to the data used in this example

In this example I've used the 'Sample Sales Data' dataset (click here to download), it has 3 columns containing the following:
Date - Random date and time between 2011 and 2013
Number Sold - Random whole number between 10 and 10,000
Value Sold - Random, 2 decimal number between 100 and 10,000


And also used the Dax Date table from my previous blog post:
DateTable = Generate(CALENDAR (date(2010, 1, 1) , today()),
row("DayNo",day([Date]), // Date for calculations
"Day ddd", Format([Date], "ddd"), //day name, Mon, Tue, Wed, etc
"WeekNo", WEEKNUM([Date]), // Gregorian week number
"WeekDay", if (or (Format([Date], "ddd") = "Sat", Format([Date], "ddd") = "Sun"), 0, 1), // 1 if the date is a week day
"WeekDayNum", WEEKDAY([Date], 2 ), // Number from 1-7 based on the week day
"Week", [Date] + 7 - Weekday([Date], 2), // Last date of the week
"MonthNum", MONTH ( [Date] ), // Number of the gregorian month
"MonthName", FORMAT ( [Date], "mmmm" ), // Long name of current Month
"MonthEnd", EOMONTH( [Date], 0), // Date of the last day of the month
"Year", Year([Date]) // Year of the base date
))




Labels:

Comments

Post a Comment

Popular posts from this blog

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...
Post a Comment
Read more

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...
Post a Comment
Read more

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article)  Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...
5 comments
Read more