Cumulative Columns and Row Subtraction

I recently worked on a project which involved taking a csv file and pushing it into Power BI. Nothing unusual here, you would think. The challenge was the data in the file being provided. The only value presented was cumulative, on a day by day basis. What was required was what happened each day.

As there was no opportunity to change the upstream process, I needed a way of doing this in Power BI. What follows is a step-by-step to deriving the actual column above within Power BI. At the end of this post is a link to the source file I used and the completed pbix file.

To begin, import the file and call the table source. Import the file again and call it lookup. Now, the interesting stuff starts...

Go into edit queries, it should look a little like this:

With the Source query selected, go to Add Column on the ribbon and choose Add Index Column and then From 0. Now choose the Lookup query and do the same again, this time starting the index From 1. Close and apply the changes.

Back in the designer view, click edit relationships from the icons on the left bar, which should look a little like this:

To create a relationship between index in Source and index in Lookup simply highlight the field in Source and drag onto the field in Lookup. Hovering over the created relationship you should see index highlighted in both tables.

More complex relationships can be defined if required, but a 1 to 1 relationship is sufficient for this purpose.

Now for the fun bit, creating a DAX calculation that will fetch the cumulative value from the lookup table and subtract it from the cumulative value in the source table. The relationship between the two indexes defined earlier means that we are effectively deducting the next row from the previous row in the source dataset.

To begin click new column from the Modelling tab, make sure you have the source dataset highlighted, this is where the new column will be created and also sets a context for the query:

Now to  create the DAX formula in the calculation bar, which looks like this...

Actual = Source[Cumulative] - SUMX(RELATEDTABLE ('Lookup'), [Cumulative])

Actual is the name provided for the new column. Source[Cumulative] returns the Cumulative column from the source table. SUMX(RELATEDTABLE ('Lookup'), [Cumulative]) returns the Cumulative column from the lookup table. You should end up with a new column called Actual that looks like this.

One last thing to tidy up, the last row of the source dataset returns -70 in the actual column, which is going to mess up any visualisation we plot against it, so let's get rid of it. From the Home tab go to Edit Queries. Choose Remove Bottom Rows from the Remove Rows drop-down. Enter 1 to remove just the last row and that's it.

Samples:

You can grab a sample XLSX file here. The completed pbix file is here.