Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
39 | |
20 | |
12 |