Granularity — A cure for sparse dates using Power Bi DAX
Ever been in that box when your are working with a time series data set and you hit sparse dates, shit! this is beans and it is the tears emoji for me. This is how it you can overcome it with Power BI DAX.
Mostly, people who work with financial and sales data are very familiar with this problem. This is a nightmare for a Data analyst especially on a team without a Data engineer.
Working with Sparse dates can be easy, you just need to demonstrate a little bit of your knowledge in granularity, date intelligence for DAX and data modelling, then you can have a stress free day.
What are Sparse Dates and why do businesses encounter these problem?
Sparse dates means that there are gaps present in the dates being recorded in a transaction period or a time period. For example, Sales was made on the 13th of February and the next sales occurred on the 18th of February, in the human eye, this makes sense but in the eyes of that analytic tool — there’s an uttermost confusion wondering what happened and why there’s a gap. I need you to understand that this process does not affect the quality of your data, but it makes it stronger, flexible and easier for you to see more insights in the story.
How to treat sparse dates
The cure is just granularity.
What is Granularity and why introduce it ?
Think of tiny things, texture, break a bottle it turns into pieces, now do you imagine granularity? Granularity in data is data broken down into its smallest bit, so in terms of dates, I mean a detailed data of what happened in days, months, quarters, hours, seconds etc. When data is collected at a detailed level, it is easier to see through the story. But not all data can be granular, and this is because the granularity of your data is usually driven by your business objective.
How can this be achieved using Power Bi?
The Power Bi tool, is a data visualization tool designed by Microsoft. I’ll be using the personal finance data set to demonstrate. Import personal finance as an excel document and select load. This is because we are not performing any data cleaning on this data set.
At the extreme left corner switch from the report area to the data area. In the this data set,we see transactions that occurred and the dates when they occurred. Now when you take a look at these dates, you will see that transaction occurred on a certain day and no knew transactions until the next three days etc., also the dates on the data set starts at 22nd January, 2018 and ends on 30th September, 2019, so it is not a complete year, but it could be a complete financial year and obviously Power Bi doesn’t know that. Looking at this, the best thoughts that come to mind is to work with the transaction date column, if we do this, issues will arise when trying to see more insights.
The First thing to do is to create an independent date table, making two tables to work with. It is very easy in Power Bi, as we just need to create a calculated Table. A calculated table is a like a calculated measure and column but it is only different in its functions. To create a calculated table in Power BI, from home or on modeling ribbon, select New Table.
The function bar will pop up, if you are very familiar with excel and you understand how to read an excel syntax then working with DAX in PowerBI is nothing but a smooth transition. The formula bar is where we write our DAX function using Calendarauto(), it returns a table with a contiguous set of dates. This will return our dates from the 01/01/2018- 31/12/2019.
Date = CALENDARAUTO()
In the link, you will find multiple DAX calculations in Power Bi for date intelligence. Once you input the DAX and press Enter, Power BI will create a new table called Date. Still on the calculated table called dates, we are going to populate it on detailed levels as this will help us see more insights based on what is going on in our transactions.
The second column we are going to have on the table is the year, so obviously a year calculation in DAX. From home, click on “new column” and write the DAX, this will return the year to every date in the date column. We are going to repeat this action for every level of detail
Year = YEAR('Date'[Date])Day = day(‘Date’[Date])Month Number = month('Date'[Date])
When it comes to calculating the month in DAX, you can pay more attention to how you want the month to be for example ‘MMM’, to achieve this we are going to pass the month function into the “format” function and state how we want it to appear. This will return our month from the date column with the first three letters.
Month = FORMAT(‘Date’[Date],”MMM”)
Quater = "Q" & FORMAT(QUARTER('Date'[Date]),"")
The quarter function will return Q and the quarter no for that quarter.
The last step for this treatment is modeling. Modeling data is about establishing and maintaining relationships so that you can effectively tell stories. To perform data modeling with Power BI, select the model area. Now we have two data tables and all we need to do, is to create a connection between the transaction and date tables. Drag date from the Transaction table as it is a fact table and drop it on date in the date table. After this action, a many to one cardinality will appear and you are good to fly.
Pro tip: If your financial year for example: starts from the 19th of April, In my opinion why work with the the in built date hierarchies , it is redundant so turn it off and use the date table instead.To work effectively, you can set a filter to mark your real financial dates as true and every other date as false. For example, if the date is greater and equals to the maximum transaction date, then true or false
financial_dates = 'Date'[Date]<=MAX('Transaction'[Date])
This cure helps you to analyse sparse dates effectively but if your company is looking to focus on more on insights in real time, I think developing a structured time series database is the way forward and for this your company will need a data engineer as it is not the job role of a data analyst or scientist.
If you had a good time reading this, give it a clap. You can follow me on medium @deborahosilade