I had an interesting request from a client recently, they wanted to work out how frequently their customers ordered products. Putting this into "database speak" they wanted to calculate the number of days between customers' transactions. Clearly, in order to do this calculation, I needed to find the transaction date that was before the transaction date sitting in the current row. 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 the previous row, for example the difference in quantity between two consecutive transactions.
Here's an example of the calculated columns I'm talking about:-
I've filtered out Customer ID 7 and sorted on the Sale Date column so you can see the calculation is correct, but the sorting and filtering makes no difference to the outcome of the calculation.
This then was the answer I gave to our client. The "Days Between" column calculates the days between the date in the previous row and the date of the current row for each customer and uses this expression:-
Note that this calculation returns a column of a DATE data type so you will need to edit the data type to WHOLE NUMBER
So we 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, using "Sale Date" to define which row is previous. The only additional step (in the variable "previousqty") is to find the QTY that is in row that has the Sale Date equal to the previous transaction date.
This is the DAX expression (note the table name is "Sales"):-
For this expression to work, all you need is a column that holds ascending values for each row as in the case of "Sale Date". Any other ascending number would do, such as an index number.
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.
If you want to learn more DAX and create measures like the ones above, we run regular Power BI courses in London, or we can run an on-site course customised for your company. See our Power BI Training page for public course dates, or contact us for an onsite training quote.