Blog

Recently I was asked by one of our clients to solve this question; how could they calculate the number of days between customers’ transactions? Clearly, in order to do this calculation, you would need to find the transaction date that was previous to the transaction date sitting in the current row. You could then subtract the earlier date from the later date and return the days between them.

It occurred to me, that such a calculation could have a more general application; not necessarily just calculating days between two transaction dates but it could be used to calculate any difference between values sitting in the current row and values in the previous row, for example the difference in quantity between two consecutive transactions.

Here’s an example of the “Qty Difference” and “Days Difference” calculated columns I’m talking about:-

Image

In the above example, I’ve filtered out CUSTOMER ID 7 and sorted on the SALE DATE column so you can see the calculations, but the sorting and filtering of the data in the table makes no difference to the outcome of the calculation.

In order to find the Previous Row to the current one we need to start by numbering the rows in ascending order based on the Sale Date. It would then be possible to find the previous row because it must have a number that is immediately less than the number sitting on the current row. To create this ascending number , I used Power Query to firstly sort the data by the SALE DATE column and then add an INDEX column:-

Image

Once I had generated this Index column, I could use it to calculate the “Days Difference” column which calculates the days between the date in the previous row and the date of the current row for each customer. This is the DAX expression in the calculated column:-

DAYS DIFFERENCE = VAR myindex = Sales[INDEX] VAR mycustomer = Sales[CUSTOMER ID] VAR previousindex =     CALCULATE (         MAX ( Sales[INDEX] ),         FILTER ( Sales, Sales[CUSTOMER ID] = mycustomer && Sales[INDEX] < myindex )     ) VAR previousdate =     CALCULATE (         MAX ( Sales[SALE DATE] ),         FILTER (             Sales,             Sales[INDEX] = previousindex                 && Sales[CUSTOMER ID] = mycustomer         )     ) RETURN     IF ( previousdate, Sales[SALE DATE] - previousdate )

Note that this calculation returns a column of a DATE data type so you will need to edit the data type to WHOLE NUMBER
In the “old days” we would’ve had to use the DAX function, EARLIER to find the values on the current row but now we can just use DAX Variables.

In this expression, the first two Variables, “myindex” and “mycustomer” find the Index number and the Customer ID sitting in the current row.

The “previousindex” variable finds the previous Index number from the current row. It does this by filtering the Sales table to match the customer on the current row and also any rows that have an Index number that is less than the Index of the current row. Of these filtered Index numbers, the MAX function finds the largest of these that is less than the Index number sitting in the current row i.e. the Index number previous to the Index number in the current row.

Then to find the date sitting in the previous row, the “previousdate” variable filters the row in the Sales table where the Index number is the same as “previousindex” and the customer is the same as “mycustomer”.

Finally, we just subtract the date on the current row from the date sitting in the previous row. The IF function ensures there will be no calculation if there is no previous date i.e. this is the customer’s first transaction.

So we now could use almost this same DAX expression to calculate the “Qty Difference”. This calculated column finds the difference between the quantity on the previous row for each customer and the quantity on the current row:-

QTY DIFFERENCE = VAR myindex = Sales[INDEX] VAR mycustomer = Sales[CUSTOMER ID] VAR previousindex =     CALCULATE (         MAX ( Sales[INDEX] ),         FILTER ( Sales, Sales[CUSTOMER ID] = mycustomer && Sales[INDEX] < myindex )     ) VAR previousqty =     CALCULATE (         MAX ( Sales[QTY] ),         FILTER (             Sales,             Sales[INDEX] = previousindex                 && Sales[CUSTOMER ID] = mycustomer         )     ) RETURN     IF ( previousqty, Sales[QTY] - previousqty )

So this is what I love about DAX. Once you find a solution to a specific calculation, you can often then use that DAX “pattern” to solve a number of other calculations.

Benefit from expert training

At Burningsuit we have years of experience in providing expert, well-rated training courses in Power BI, DAX and Excel. We’re adept at providing the information and skills you need to make the most of Power BI together with your data and offer on-site and public courses across London and the UK.

To contact us, please call on 0800 0199 746 or use our Contact page. We’re always happy to discuss Power BI training and implementation.