Hello readers!
Lets go through of one the use case of EXCEPT function.
Problem Statement: To find the count of lost customers with respect to previous month and date selected in sliver visual.
Solution:
There are two tables namely Calendar and Sales by Store and they have a relationship between them based on transaction date column.
Step 1:
To calculate count of customers till date. Date is selected from slicer visual available on the report page.
The count of customers till date is calculated by using VALUE() function which will return list of distinct values in a column. This part is calculated in a variable.
Step 2:
To calculate Customer Count of Previous Month. This is calculated by using VALUE() function and DATEADD() function which will help to fetch last month’s data. This part is calculated in a variable.
Step 3:
Here we are calculating the count of lost customers with respect to last month i.e. customers who were there in last month and are not customers in this month. Month is selected based on the date selected in the slicer visual.
EXCEPT function is used in the measure below. It excludes the data of previous month from the data till date. It returns table so that’s why we need to use COUNTROWS() function to count the rows of the output table.
Step 4:
In the visual below, slicer style is Before. Lost customer count is shown.
Note: For demo purpose, Customer_Count_till_date and Customer_count_of_prev_month variables from the above measure are created as separate DAX measures. These measures are dropped in the visual for better understanding. City is the dimension by which we are viewing the data.
Conclusion:
Based on the date selected, we can see the lost customers count.