Compare Time periods using a Date Table in PowerBI

Use a Time Master Data

In my previous blog post about PowerBI Best Practices, I insisted on the importance to separate your Measures from your Attributes and to store Attributes in Master Data tables.

This allows you to slice your data on several tables and therefore on several visuals at the same time, which is what you very much want to do when exploring data with PowerBI.

When we speak about Attributes in a Master Data table, we usually speak about Products, about Stores, but we often forget an important dimension: TIME!

In fact each Time Period could be the Key to a Master Data table and come with Attributes as well. We recommend you build such a Date MasterData table and connect it to all the tables with your measures. Here are some examples on what you can do with this:

Example 1: YTD comparison

Let’s say we are in October 2017 and we want to compare YTD (Year To Date) Sales meaning the Sales of January 2017 to September 2017 vs. the same period last year (Jan 2016 to Sept 2016). That is a very common problem, so how can we handle it with PowerBI?

One of the top solutions is actually to create a Time Master Data table which will take each date as a Key and, with a YTD Index which will take “YTD N” as value for the dates in the YTD of the current year (Jan 2017 to Sept 2017) and “YTD N-1” as value for the dates in the YTD of the year before (Jan 2016 to Sept 2016).

Then you can compare your Sales YTD 2017 vs. 2016 building the following measures:

Sales_YTD_N = CALCULATE (SUM (Sales), MonthyMaster[YTDIndex] = “YTD N” )

Sales_YTD_N_1 = CALCULATE (SUM (Sales), MonthyMaster[YTDIndex] = “YTD N-1” )

YTDSales_% = [Sales_YTD_N] / [Sales_YTD_N_1] – 1

Example 2: Comparing YTD monthly trends on a Line Chart

Let’s say we would like to build the following Line Chart for our Sales, with the month as X-axis and the Sales per Year as Y-Axis

Capture d_écran 2017-10-30 à 20.16.57

Again the Monthly Master data could come handy, if we build some columns Year and Month as dates attributes in the Monthly MasterData

Other Examples

You can also use the last Closed month Index, the current month index, Quarters, Half-Years, rolling indexes and so on…

One I find really useful is the Relative Month index. It works as follow:

  • 0 for Current month
  • 1 for next month, 2 for Month N+2 etc…
  • -1 for last past month, -2 for 2 months ago

This is quite useful for building rolling indexes. Let’s say you want to calculate your sales for the last 12 months. You can do this with PowerBI using the following DAX formula

Sales_Last12m = CALCULATE (SUM(Sales), MonthlyMaster[RelativeMonth]<=-1, MonthlyMaster[RelativeMonth]>=-12 )

OK, you convinced me to user a Time Master Data, now how do I get one?

There are many mays to build Time indexes, especially using M language in the Query Editor

I think this is by far the best option for many reasons:

  • a date Master Data easily fits in an Excel or CSV file
  • it’s not report dependent. You can use the same table for many reports so why not build it once for all and import it in 2 clicks in every Report you will use?

I will give you the file I use for Monthly MasterData, it provides the following:

  • YTD Indexes
  • YTG Indexes
  • Last Month Indexes
  • Current Month indexes
  • Month
  • Year
  • Next and Previous 3, 6, 12, 18 months rolling
  • Relative Month which gives the relative position of other months compared to the current month

To update all indexes dynamically, you need to each month: open the file, click on the Button to run the VBA macro which will update all the values of the attributes dynamically. That’s done. Save, go to PowerBI and just Refresh the file

MASTER_DATA_DATE.xlsm

And you, how do you handle your time analysis?

AnalystMaster

 

Convert Matrix into Flat File format ( Reverse Pivot Table)

We have seen in this article the importance of working with Flat File. This is especially true for working with PowerBI but not only

Reminder: what is a Matrix and what is a Flat File format

Matrix Format

Date 1 Date 2
Product 1 1000 1100
Product 2 500 450

Flat File Format

Products Dates Sales
Product 1 Date 1 1000
Product 1 Date 2 1100
Product 2 Date 1 500
Product 2 Date 2 450

Converting from Matrix to Flat File

We have seen that converting from Flat File to a Matrix is generally seemless. Excel Users know how to do it well using Pivot Tables for example.

However the opposite is not straightforward (at least used to be…). This is annoying since the Matrix format is one of the most used by Excel users and I have no doubt you will receive most of your data in this format.

So how do make this conversion from Matrix to Flat File? Here are some options:

  • With PowerBI: We have told you you need to stop Excel and switch to PowerBI a long time ago. PowerBI’s Query Editor handles this conversion just fine:Just import your data and select ALL YOUR ATTRIBUTES columns (i.e the ones you do NOT want to “unflip”), then right click (your mouse cursor needs to be on the header of one of the attributes) and select Unpivot Other Columns
    BOOOM! Job done!

Before

After.png

  • With Excel 2016 / PowerQuery: Similary Excel 2016 comes with PowerPivot and PowerQuery built in. You just have to follow these steps to convert your Matrix into a Flat file, it’s very similar to the PowerBI technique
  • With old versions of Excel:  this post will show you how to do it

Enjoy!

AnalystMaster

 

 

The 3 rules of a good PowerBI model


 

So that’s it you have downloaded PowerBI Desktop and are now ready to start your first PowerBI report. Congratulations!

Like any architect you need to get the foundations of your PowerBI report right.

There is a lot of training material on the official PowerBI Training page to learn about PowerBI to get you started, however we’d like to give you some Key Best Practices to build PowerBI reports.

Rule #1: Import data with maximum level of detail

One day the CEO of my company asked me how a new product we just launched was doing. I extracted the Sales Worldwide vs. the Global Forecast we had, made a simple comparison and handed it over to him. He got his answer: the launch was doing below expectations, and then fired me with some more questions: “can I get the performance by market?” I went back to my desk and re-extracted the data with markets as a level of detail, re-did the analysis and handed it over to him. I thought that was the end, until one minute later he asked: “how about the performance by color? and I would also like to know which color is doing well in which market!”

I should have known better, and be prepared for these natural questions. Now imagine that instead of going back to my desk at each question and coming back to him, I had extracted all the data directly with maximum level of detail (by market and by color). I could have answered his questions straight away, and not only it would have saved both of us time, it would have had blown his mind!

Be smarter than me and make sure you always extract your data with maximum detail before you import it into PowerBI.

After all PowerBI is a true BI tool, you are not limited by the amount of data you can put in it so why limit yourself by importing data already aggregated? Import it with maximum detail (unaggregated) and let PowerBI aggregate it for you to let you explore all the levels of details you need.

So take the time to prepare your data to make sure you will import it with the maximum level of detail available to you, this will prove a valuable investment!

Rule #2: Embrace the Flat File Format!

PowerBI works best with “flat files” tables as an input. This can be destabilizing for most Excel Users who are comfortable with the “matrix” format in Excel. First let’s clarify what is a Matrix and a Flat File on a simple example of Sales by Product and by Date:

Matrix Format

Date 1 Date 2
Product 1 1000 1100
Product 2 500 450

Flat File Format

Products Dates Sales
Product 1 Date 1 1000
Product 1 Date 2 1100
Product 2 Date 1 500
Product 2 Date 2 450

Before uploading your Data in PowerBI, make sure that all your data is in Flat File Format. If you have it in Matrix format, you might need to convert it to Flat File before uploading it into PowerBI.

Why? Because you will see it’s much better to have “1 column per type information”. In the Matrix format above you have the same information (Sales) in several columns. The only difference between the columns is the date. With PowerBI it’s much more efficient to have everything in 1 column and if you need to access different dates you’ll use the famous CALCULATE + SUM formula (equivalent of SUMIF for our Excel fans)

Sales_ProductX_MonthX = CALCULATE ( SUM (Sales[Sales], Product = “Product X”, Date = Date X )

Whereas going from Flat File to Matrix format is easy with Excel (just do a Pivot Table from the Flat File), going from Matrix to Flat File is surprisingly not straightforward, and we will cover this topic in a dedicated post.

Rule #3: Separate Measures and Attributes

So now you have imported some flat files. You therefore have a bunch of tables, with a bunch of columns (fields) in them. It’s important to make the distinction between the three types of columns (fields) you can have: Keys, Measures and Attributes

  • Keys (or ID): this is what identifies a record and make it unique. For example if you have a table with Sales by Product and Date, Product and Date are the Keys because the combination of a Product and a Date is what defines a unique record. Sales will be a measure.
  • Measures (or KPI): a measure is essentially a field that can be “measured”, so usually with a numerical data type. For example Sales, Orders, Forecasts, Budget… would all be “measurable” fields. PowerBI usually automatically detect them and display they with a little Sigma sign next to their description
  • Attributes: All other fields non measurable are called Attributes. For example it could be the Product Line, Size, Color, the Country or Geographical Area of a Store.

OK easy, so why am I bothering you with this? You will see that it’s best to keep your Measures and Attributes separated as much as possible. That means you should have only Key + Measures tables (KM), and Keys + Attributes (KA) tables:

  • Key+Measures tables (KM tables): Do not import Attributes with Measures. We recommend that you avoid mixing them with attributes in the same table. Just prepare a table with the Keys identifying your record (Product, Date, Store code…for example) and the measures associated to this Key (Sales for example). Avoid polluting this table with attributes such as Product Line, Store Country etc… and import them separately
  • Key+Attributes tables (KA tables or “Master Data”): We recommend that you store your Attributes separately in what we will call Master Data tables, and that you connect in the Data Model the Keys of the KM and KA tables together

Let’s take a simple example where we want to analyze our Sales and Stock by Product Line and Country:

Poor Data Model: Attributes mixed with Measures

Capture d_écran 2017-10-29 à 14.53.45

So here you see that you have the Prod. Line and Country information in both Sales and Stock tables.

Nothing wrong at this point, but if you are going to build your model in that way, it is very important to be clear on what PowerBI can and can not do.

What PowerBI can do:

  • breakdown your Sales by Prod. Line and Country using the Prod. Line and Country attributes from the Sales table

Sales_OK

  • breakdown your Stock by Prod. Line and Country using the Prod. Line and Country attributes from the Stock table

Stock_OK

What PowerBI can NOT do:

  • breakdown your Sales by Prod. Line and Country using the Prod. Line and Country attributes from the Stock table. If you try to do so the same value will be returned for all Prod. Lines or Countries because PowerBI will have no clue how to break it down

Sales_KO

  • breakdown your Stock by Prod. Line and County using the Prod. Line and Country attributes from the Sales table. Same here, the value returned is the sum

Stock_KO

What does THAT mean? That means that as long as you want to work with either Sales or Stock information INDEPENDENTLY you are totally fine:

  • if you want to look at Sales and break them down by Prod. Line and Country, use the Prod. Line and Country attributes from the Sales table
  • vice versa if you want to look at Stock and break it down by Prod. Line and Country use the Prod. Line and Country attributes from the Stock table

But that’s quite limited don’t you think? What if you want to COMBINE Sales and Stock info by Prod. Line and Country, for example to calculate Stock Coverage by Prod. Line and Country with the below measure:

StockCoverage = SUM (Stock[Stock]) / SUM(Sales[Sales])

This measure will return a correct result if we do not try to break it down by Prod. Line or Country. PowerBI will just do the sum of Stock and Sales in both tables and divide both.

However if you try to break this measure down by Prod. Line or Country, PowerBI will only break the Stock by Prod. Line and the Sales by Country. When breaking down the Stock by Country and the Sales by Prod. Line he will use the total Stock and total Sales and this is totally wrong.

You have to be careful here, because PowerBI will return you something which could “look right” at first sight, but which is “actually wrong”:

StockCoverageWrong

So how do we get it right? By storing the attributes separately in a Master Data table and liking the Master Data to the Measures tables

Great Data Model: Attributes separated in Master Data tables

GoodModel

So we’ve separated our Attributes here, and we make a link in the Data Model between the Keys of both the Measures tables (Sales and Stock) and the Master Data tables (Product Master Data and Store Master Data)

Now thanks to the link we made between the KM and KA tables, PowerBI knows how to breakdown both the Sales and Stock by Prod. Lines and Country, using the attributes from the Master Data tables. You can now use your Stock Coverage Measure safely, and the below breakdown is correct.

CoverageCorrect

By using this technique you will be able to use PowerBI not only to explore your Sales and Stock data INDEPENDENTLY but to actually CROSS information between the two. WOW!

Bonus rules: how to build your Master Data tables

Now you know that for making the most of PowerBI you are going to have to maintain some Master Data tables to store all your attributes. Here are a few rules for these:

  • MasterData Rule #1: Have 1 Master Data table per Key in your KM tables.
  • MasterData Rule #2: Have all the possible Keys in the Master Data. In our example above, that means that each single Product need to be in the Product Master Data and each single Store in the Store Master Data. If you miss some Products or some Stores, then PowerBI will not be able to calculate on them.
  • MasterData Rule #3: No Duplicate in the Master Data tables. You need to have 1 unique record per Key. Otherwise the link will not work in your Data Model.

Conclusion of this long post: if you build your models using data with Maximum detail, import your data as Flat File, and Separate your Measures from your Attributes then link KM and KA tables together you will enjoy the full PowerBI experience and avoid many mistakes. We will come back in future posts on other side advantages of using these 3 rules.

And you, do you have any other Best Practice to share when building PowerBI models?

AnalystMaster

 

 

 

 

Forget Excel – Meet PowerBI

The tool that will kill Excel… and change your life!

In my analyst life, there was a before and an after I discover PowerBI

Before with Excel: My go to tool was generally Excel. It’s a very powerful tool for Reporting and Ad-Hoc analysis, however it has a:

  • lack of connectivity: despite some basic connectors (Access…), data rarely comes to Excel by itself for analysis. You generally have to extract it manually from your systems (like SAP…), copy paste it manually, or VLOOKUP it which is a hassle
  • lack of capacity: Although an Excel spreadsheet fits 1m rows which is enough for most users, this sounds prehistoric at the age of Big Data.
  • lack of scalability:  analysis and reports are rarely carved in stone, you constantly want to add on to it: new features, new drill downs, new information that you did not think you needed before… although this is not the weakest point of Excel, it can be quite a hassle to make a big Excel report evolve without having to destroy and rebuild big part of it
  • lack of sharing options and governance: when an Excel analysis is done, how do you share it? Usually you will attach it and send it by email, or drop it on a shared drive for people to access. Again in 2017 that sounds a little outdated… but what if you would like to share only some part of the data to some users and some other part to other users? (for example you are have a global role and did a global analysis but you would like to share the US results with the US team only and the Asia ones with the Asia team only). Are you going to build 2 separate Excel files (hard coded I mean because they would easily access the global data if you just provide them with a Filter / Slicer)?

Don’t get me wrong, Excel is still an awesome tool and I still use it a great deal. The great news about PowerBI is that is that despite my provocative title it’s totally Excel compatible. As a matter of fact it is a Microsoft tool which started as an Excel add-in (for those who would remember PowerPivot) and evolved into a stand-alone BI tool

After with PowerBI:  you will:

  • connect to your data source seamlessly: there are plenty of connectors (Excel, Access, SAP, Hadoop…) available which enable you to be plugged directly on the data source without having to extract or copy paste it manually
  • transform your data seamlessly: PowerBI comes with a built in ETL (Extract-Transform Load) tool which allows to transform and prepare your data to be used, but also to memorize each step of the transformation for next time
  • manage lot of data: there is no capacity limit to the number of rows per table PowerBI can process, so it works well on very large databases
  • scale easily: the data modeling tool in PowerBI will allow you to scale up easily if you need to add more information to your initial model. It’s very easy to add tables, connect them together, and then add a few more calculations
  • build great visuals: charts and graphics are worth 1,000 words and great analysts know that it is important to have beautiful visual outputs to present your data. PowerBI has lots of it: line chart, bar charts, funnels, combo charts, pie / donut chart, maps with geographic location management… you can hardly do better. And when you think you’ve exhausted all options you can go online to the PowerBI visual gallery and download cool custom visuals developed by the PowerBI community
  • explore with dynamic drill down: This feature exists in Excel with regular Pivot Tables, however you will find out that PowerBI takes it to a whole new level
  • share and govern like a pro: with PowerBI you can share data online and users can access it online as well or via apps on their smartphone or tablet. when sharing you can define profiles and assign each user to a profile. Governance you said? Sorted!
  • have fun! Whereas building an Excel report can be quite boring, you’re likely to discover that doing the same thing with PowerBI is fun, playful and exciting! No doubt you will wow your coworkers, boss and IT department when they see you displaying your slick reports on your smartphone!

 

Or because pictures speak 1,000 words that’s how my reports looked

Before with Excel

monthly-operational-budget-report

After with PowerBI

8507.LiveReportTile

OK Excel is so 2000… but why not use a different BI tool?

If you have access to them (because they are not free…), there are a great deal of BI tools available today (Tableau, Qlik… to name a few), which if you master them will give you equivalent functionalities as PowerBI. You are more than welcome to use them especially if your company or organization provides them, however as we are supporting the masses of analysts out there who are left out with only Excel at their disposal, we will cover mostly PowerBI in this blog for the following reasons:

  • It’s free! The Desktop version of PowerBI can be downloaded for free from the PowerBI Desktop download page. And if you want to use the cloud service to publish your reports or for other user to consume the content you will publish out there, you can get Free licenses.
  • It’s the best! and we are not the only ones to think that way. PowerBI ranked #1 BI tool in the famous Gartner Magic Quadrant in 2016 and 2017
  • It’s almost like Excel! Did I mention that PowerBI is the evolution of successive upgrades from ExcelExcel -> Excel + Pivot Tables -> Excel + PowerPivot -> PowerBITherefore Excel users will find it very natural and easy to transition from Excel to PowerBI. It will make it very easy to teach your coworkers about it.For example for 2 of the main functions you will be using:

Excel: VLOOKUP (Value, Table, ColumnNumber to look up)
PowerBI: LOOKUPVALUE (Column to look up, Table, Value)

or

Excel: SUMIF ( Column to sum, Filter)
PowerBI: CALCULATE ( SUM (Column to sum), Filter)

  • It’s getting better every month: The PowerBI teams releases monthly upgrades to their online service and to PowerBI Desktop. The pace of innovation is quite crazy, think about it: it’s as if you were getting a new version of Excel every month! you can discover more about the different updated on the PowerBI blog
    PowerBI is also a tool built based on the feedback of a community of thousands of users who are proposing ideas and voting for the best ones so that they can get picked by the PowerBI team to develop

So believe us, download PowerBI and start playing with it (we feel the word is not too strong). Surely you will soon be migrating all your Excel reports into it and next thing you know when asked for a quick ad-hoc analysis you will be using it as well and discover that Excel is really a tool from the past and that your life has changed…

We will cover more in detail PowerBI in next posts, stay tuned!

AnalystMaster