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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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

image

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.

image

As you can see below, the column MTD.1 contains the MTD sales as of yesterday.  To make it clearer, I then renamed this column to be called “MTD Yesterday” like I did before (see 1 below).image

I then deleted the 2 ID columns to give me a bit more space.  Below is what I had at this point.

image

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.

image

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.

image

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.

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia.

This Post Has 10 Comments

  1. Yes nice work, when coaching a client through a messy dataset like this I would reach for a measure that included EARLIER and FILTER (bleh!). That’s a pretty steep learning curve. Your solution is much more straightforward.

    1. Very good Matt Thanks !!
      Same as you Austin 🙂
      I Probably would have used a DAX calc column with a variable though

      Var Yesterday = Table1[Date] – 1 RETURN
      Table1[MTD]- CALCULATE(SUM(Table1[MTD]) ; FILTER(Table1;Table1[Date] = Yesterday))

  2. One more option without joining tables

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}, {“Product”, type text}, {“MTD”, Int64.Type}}),
    #”Sorted Rows” = Table.Sort(#”Changed Type”,{{“Product”, Order.Ascending}, {“Date”, Order.Ascending}}),
    #”Inserted Month” = Table.AddColumn(#”Sorted Rows”, “Month”, each Date.Month([Date]), type number),
    #”Added Index” = Table.AddIndexColumn(#”Inserted Month”, “Index”, -1, 1),
    #”Prior Record” = Table.AddColumn(#”Added Index”, “Records”, each #”Added Index”{[Index]}),
    #”Expanded Records” = Table.ExpandRecordColumn(#”Prior Record”, “Records”,
    {“Product”, “MTD”, “Month”}, {“Product.1”, “MTD.1”, “Month.1″}),
    #”Daily Sales” = Table.AddColumn(#”Expanded Records”, “Daily Sales”, each
    if [Index] = -1 then
    [MTD]
    else if [Product] = [Product.1] and [Month] = [Month.1] then
    [MTD] – [MTD.1]
    else
    [MTD]),
    #”Removed Cols” = Table.SelectColumns(#”Daily Sales”,{“Date”, “Product”, “Daily Sales”})
    in
    #”Removed Cols”

  3. I really need to find the time to make some posts on here….I had a similar issue with trying to take a forecast done by Month and wanted to convert that to a working day only calendar so that I could do MTD and YTD comparisons to actual. My method was to merge with a date table within PowerQuery……some clever unpivots and a little MDX gets you there surprisingly easily! All that said, LOVE Matt’s logic and clarity on these….(as I do Rob’s, Avi’s, etc on and on)!

  4. Not sure if you are aware, but you can Ctrl + Click columns in the merge query window in order to do mult-key joins. Just Ctrl + click the top query columns, and then in the same order Ctrl + click the bottom query. Also, you can eliminate some of the formula complexity (especially after doing the Ctrl + Click above) by just doing a:

    if [MTD Yesterday] is null then [MTD] else [MTD] – [MTD Yesterday]

Leave a Comment or Question