Sometimes (dare I say often, or even usually) the data you have available is not in the ideal shape for analysis in Power BI. These problems can manifest in many different ways, however one common problem is that the data you have access to is in a cumulative format such as Month To Date rather than a daily format. Consider the following sales data.
There are sales each day, but the daily sales are not provided as part of the source data. If you want to know what the sales were for 2nd Jan, you would need to subtract the MTD value 15 from the previous day’s value of 8 to return sales for 2 Jan being 7. And so on for every other day in the month. Now it is possible to use this data as is by treating the data as semi-additive and using a semi additive DAX measures like the ones you can read about here http://www.sqlbi.com/articles/semi-additive-measures-in-dax/ . I personally prefer to change the data before loading so that I have a simple daily sales table like the following.
Of course you could do this in Excel, but that would involve maintenance each time you get a new extract. A much better way of solving this problem is to use Power Query to transform the data on load (actually the best way is to get the daily sales data from the source, but that may not be possible). Here are the steps to complete this process (one way to do it anyway). I am using Power BI for this demo.
Overview of the Approach
The way I will handle this problem is to load the MTD data twice, with a second column showing “MTD as of Yesterday” along side of “MTD Today”. Once these 2 numbers (MTD today and MTD Yesterday) are next to each other, the process of calculating the daily sales will be easy. The only tricky bit is to catch the change of Product Codes and the change of Months. Anyway, here we go.
Connect to the Source Data
First I connected to the source data and went into Edit mode. You can download my source data here if you want to play along.
Add some Index Columns
Next I added 2 index columns offset by 1. This process has been made easier in a recent update of Power Query as it is now possible to add an Index column starting as base 0 or base 1 as is shown below.
Before I added the Index columns, I applied a double sort on the data. I first sorted the Product column and then the Date column. This ensures that I have all the relevant rows of data in chronological order. I then added 2 Index columns, one starting from base 0 and another from base 1.
The names of these new columns are not very descriptive. It is possible just to rename them using another Power Query step, however I always encourage my students to turn on the formula bar and look at the PQL code that is generated by the UI. As you can see in the image above, you don’t need to be a programmer to work out that you can edit the previous step and replace the default “Index.1” name with something more descriptive. So after modifying the column names in the 2 existing steps, I have the following. The names are not really that important actually, but this is how I did it.
Create a Month Column
The next thing I did was to create a month column. I need this to detect when the month changes. This is easy to do. Simply select the Date column and then follow the steps shown below.
Merge the Query with Itself
The next thing I did was join the query back onto itself using the 2 different Index columns to create the “yesterday” offset. I simply clicked “Home\Merge Queries” and then set up the merge as follows
Note how I selected to merge the table with itself (1 above) and then selected to join on the columns ID Today (2 above) with ID Yesterday (3 above). Of course there is 1 less match than total rows in the data set because the first record in the table does not have a “yesterday” record.
Expand the New Column of Data
I then expanded the new column of data to extract the product, MTD sales and the Month as follows.
I then deleted the 2 ID columns to give me a bit more space. Below is what I had at this point.
Create the Daily Sales
Now that the data is loaded twice in 2 columns as shown above, it is quite easy to write a custom column to create the daily sales. I added a custom column as shown below.
if [Product] = [Product.1] and [Month] = [Month.1] then [MTD] - [MTD Yesterday] else [MTD]
Clean Up and Load the Data
The last thing I did was remove all the columns I didn’t need leaving me just with daily sales data as shown below.
You can download my sample data (Excel format) here. This workbook also contains the full Power Query steps if you want to take a look.