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 everyone,
I want a cumulative count of my active clients, to show in a KPI and/or line chart. I have a measure that shows my active clients (see below), but i need it to be linked to the start and end of the client agreement.
The measure i now have:
Active Clients = CALCULATE( COUNT('Clients'[Client Code]), FILTER('Clients', 'Clients'[Price Agreement State] = "active"))
It shows me the correct number of clients but when I put it on a line chart, it doesn't show me the cumulative count. So I think I need something like, If today is between start agreement and end agreement, client is active. because start and end agreement are marked as dates. How do I translate this to DAX.
|
The visual i get now:
Solved! Go to Solution.
Hi,
This would be your code:
CountActiveEmployees =
var __svym = MAX('Date'[Date])
return
CALCULATE(DISTINCTCOUNT('Table'[Client Code]);FILTER('Table';__svym>'Table'[Start Agreement] && __svym < 'Table'[End Agreement]))
As seen here:
File is available here.
Please mark as solution if this works for you. Appreciate a thumbs up for the effort.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi,
This would be your code:
CountActiveEmployees =
var __svym = MAX('Date'[Date])
return
CALCULATE(DISTINCTCOUNT('Table'[Client Code]);FILTER('Table';__svym>'Table'[Start Agreement] && __svym < 'Table'[End Agreement]))
As seen here:
File is available here.
Please mark as solution if this works for you. Appreciate a thumbs up for the effort.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Welcome! Glad to help.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Create a measure that checks for each date on your x axis and for each account if the selected date is between the agreement start and end dates.
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |