Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am working on a report where I need to calculate the Strike Rate of the rep. E.g. He has 10 Customers, and in the specified period he has sold to 7 of them, therefore his strike rate is 70% (Customers sold to in this period divided by Total Customers.). This is easy enough to do, but the difficulty occurs when trying to go back in time. How many customers did I sell to in 2020 (Easy!: Distinct count of customers from sales Table), and how many customers did I have for that period (this is where I get stuck)?
I have a "Created Date" field in my customer Master table, which I thought I'd use to compare against the selected period. But here lies the problem: If a customer's created date was for example in 2019, the selected filter of 2020 would disregard this customer, which I don't want it to do, as it should count ALL customers from the beginning of time up until 2020.
So if I have a date slicer, and I choose the option "Before" (I.e. From date is locked in on 1st date in my dataset, and I can only move the TO date around), I get the desired outcome. How do I make a Year slicer act in a similar way?
Thank You!
The person started with 60 Customers
The person added 11 Customers to his database so I want this figure to be 71 (60+11)
The person added 1 Customer to his database so I want this figure to be 72 (60+11+1)
If you have a relationship between your date table and your customer created date you can create a total customers measure like
Total Customers =
var maxDate = MAX('Date'[Date])
return CALCULATE(COUNTROWS(Customer), REMOVEFILTERS('Date'), 'Date'[Date] <= maxDate )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |