In this unique example, I’ll show you an advanced DAX technique in Power BI wherein we compare a sale on any particular day to the very last sale that was made. You may watch the full video of this tutorial at the bottom of this blog.
This will be relevant if you don’t actually sell something every day or if you’re looking at a particular customer or a product and you want to evaluate, “We made a sale on this day, but how does this compare to the very last time we sold to this customer or we sold this product?”
There are no set time intelligence functions that enable us to do this in Power BI. So this is a perfect example of how you can combine multiple DAX functions to enable this type of advanced DAX insight.
Using The Sales Table Instead Of The Dates Table
We do not do this from the Dates table. Here we use Purchase Date, which actually comes from our Sales table.
In this Purchase Date column, we have every transaction or iteration of something that has occurred in our fact table. We place this in a table together with our Total Sales. Based on any selection that we make, whether it’s a customer or a product or any filter, it’s only showing the sales that we made and the actual date that they were purchased.
From here, we want to look at any particular result, and then jump back and work out the result from before. We can then compare it. And so that’s what we’re able to achieve with the formula that I have made, Sales Last Purchase.
We can now compare this $1,638 sales, for example, to the next time that a sale is made for this particular customer (Adam Hunter) because it’s now in this new context. Now let’s check out the formula wherein I used an Advanced DAX technique without Time Intelligence.
No Time Intelligence DAX Formula
The first thing I did was isolate the last sale date, the prior date that we sold. So based on whatever context or row we’re in, we’ll be able to look back and work out what that date was. For instance, we’re looking at the result $947, we’ll see the date when the $947 sale was made.
To work that out, I placed this Prior Date inside a variable (VAR).
So this is looking through every single date and working out whether that date is below the current date. That’s what MAX function is doing within this filtering (FILTER) function. It’s returning the current date that the sale is being made.
Now that we have that, we can then feed that in via another filtering function to return a particular result in a different context.
Under RETURN, we calculate Total Sales. However, instead of calculating on a particular day that it has been made, we’re going to look through every purchase date and only return the purchase date from the PriorDate via FILTER.
That’s going to produce the new context for the calculation, and then return the Total Sales. That’s how we drag the sales from the prior day to the current day. In other words, that’s how we get the sales from the last time we sold a product to a customer into the current context.
Conclusion
In this tutorial, I have demonstrated how we can compare sales by using an advanced DAX formula in Power BI without Time Intelligence. From this insight, we can branch out even more and look at things like what was the difference or what the percentage change between these results was.
If we’re able to set this up in our models and within tables or other visual types, there are many other ways that we can utilize this technique to find even better or greater insights.
This is quite an advanced concept. It is one way you can combine many different functions, including the use of variables.
There’s plenty to learn if you can find the time to dive in and understand the content that I go through.
Cheers!
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
[youtube https://www.youtube.com/watch?v=WLdPx0c2vDU?rel=0&w=784&h=441]
***** Related Links*****
Time Comparison For Non Standard Date Tables In Power BI
Using Variables In Power BI – A Detailed Example
Last Purchase Date in Power BI: When Did Your Customers Make Their Last Purchase?
***** Related Course Modules *****
Unique Analytical Scenarios
Time Intelligence Calculations
Mastering DAX Calculations
***** Related Support Forum Posts *****
Compare One Sale Versus The Last Sale (No Time Intelligence)
Sales vs Target Matrix Comparison Issues
FILTER Function Inside CALCULATE
For more comparing sales queries to review see here…..
