cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RickPowerBI
Helper I
Helper I

Cumulative count of active clients

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.

 

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

 

The visual i get now:

Screenshot (8).png

1 ACCEPTED SOLUTION
stevedep
Memorable Member
Memorable Member

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:

eompl.jpg

File is available here

 

Please mark as solution if this works for you. Appreciate a thumbs up for the effort.

 

Kind regards, Steve. 

View solution in original post

4 REPLIES 4
stevedep
Memorable Member
Memorable Member

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:

eompl.jpg

File is available here

 

Please mark as solution if this works for you. Appreciate a thumbs up for the effort.

 

Kind regards, Steve. 

@stevedepThank you very much, this was exactly what I needed.

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors