In this example I’m going to run through how you can calculate how many customers you sell to through time using a few functions inside of Power BI in the DAX language. There are actually a few ways you can calculate it, which is why I wanted to create a short tutorial on it to showcase a few things.
Understanding how to work around the data model that you have set up is crucial in this example, so I make sure to show you how I have set this up. It’s important because it’s not as easy as just placing a COUNT over your customer table. Remember, we want to show how the count of our customers changes over time, so we need to make sure the result will also be filtered by our date table.
I run through how to use the DISTINCTCOUNT function to achieve the intended result.
This produces interesting insights, and you could ultimately utilize and visualize this in many different ways. One way that pops to mind would be via scenarios analysis. You could see, based on forecasts that you might project forward from historical customer counts, how your results might be affected if you increase the amount of customers you sell to. You could run scenarios on this using an estimated average sale price to derive the average value per customer. Really powerful stuff. Hopefully you can see the same opportunities as me with this.
Another way you could visualize this type of insight is showing comparison through time. Maybe you want to cumulatively show the amount of customers you have sold to and compare that to last month or last quarter. You would start here and then ‘branch’ out using time intelligence functions.
I run through how to use all the time intelligence functions to complete this time comparison analysis in my Mastering DAX Calculations course.
Good luck implementing this one, and let me know if you have any thoughts in the comments section below.