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.
Hi all,
I have a line chart that shows my active clients per year and month. The problem is that the chart only shows how many new clients I gained that year/month and not the new total.
This is the formula I have now:
Active clients =
CALCULATE(
COUNT('Clients'[Client Code]),
FILTER('Clients', 'Clients'[Price Agreement State] = "active"))
I think the solution has something to do with the Start agreement date, but I don’t know how to translate that to DAX.
Sample data:
Client Code | Price Agreement State | Start Agreement |
2016022347 | ended | 25 February 2019 |
2015081980 | ended | 20 December 2019 |
2019073579 | active | 01 August 2019 |
2016062578 | active | 23 January 2019 |
2016032399 | active | 10 March 2019 |
2017022778 | active | 01 March 2020 |
2019073570 | active | 02 September 2019 |
So the question is;
How do I show the total clients of each year/month.
Because now I can only see how many I gained, as shown below. When I add a date to the x-axis it looks like I have a major client los in 2020, which is not correct. It only shows the new clients in 2020.
Thanks for the help in advance.
Solved! Go to Solution.
There are many ways to skin this cat. One way is to tie the agreement start date to the dates table (you have a dates table, right?!?) and to create a measure that for each selected period (month or year) calculates the agreements where the end date is after the selected period.
Pseudo code:
ActiveThisPeriod =
var ym = selectedvalue(yearmonth)
var lastdate = calculate(max(date),filter(all(dates),dates[yearmonth]=ym))
return calculate (distinctcount(agreementid),filter(all(agreements), agreements[end date]>lastdate))
Adjust as needed depending on how you want to handle agreements that flip from active to inactive inside the selected period.
The sample data that you show is insufficient for what you want to achieve. You need to have at least an EndAgreement column so that you can remove the dependency on the AgreementState column which is only a current snapshot.
Then you can show both the number of clients added per month (or per year) - with a start date in that month (or year) - and you can show the number of clients active per month (or year) - with an end date AFTER that month (or year).
Thank you for the feedback, I think I know what you mean. Which syntax should I be looking at?
Client Code | Price Agreement State | Start Agreement | End Agreement |
2016022347 | ended | 25 February 2019 | 25 February 2020 |
2015081980 | ended | 20 December 2019 | 20 May 2020 |
2019073579 | active | 01 August 2019 | 01 August 2022 |
2016062578 | active | 23 January 2019 | 23 January 2021 |
2016032399 | active | 10 March 2019 | 10 September 2021 |
2017022778 | active | 01 March 2020 | 01 March 2025 |
2019073570 | active | 02 September 2019 | 02 September 2021 |
There are many ways to skin this cat. One way is to tie the agreement start date to the dates table (you have a dates table, right?!?) and to create a measure that for each selected period (month or year) calculates the agreements where the end date is after the selected period.
Pseudo code:
ActiveThisPeriod =
var ym = selectedvalue(yearmonth)
var lastdate = calculate(max(date),filter(all(dates),dates[yearmonth]=ym))
return calculate (distinctcount(agreementid),filter(all(agreements), agreements[end date]>lastdate))
Adjust as needed depending on how you want to handle agreements that flip from active to inactive inside the selected period.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |