topic Re: Cumulative count of active clients in DAX Commands and Tips

Cumulative count of active clients

RickPowerBI — Fri, 12 Jun 2020 07:54:43 GMT

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:

Re: Cumulative count of active clients

lbendlin — Fri, 12 Jun 2020 12:44:43 GMT

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.

Re: Cumulative count of active clients

stevedep — Fri, 12 Jun 2020 16:34:14 GMT

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.

Re: Cumulative count of active clients

RickPowerBI — Mon, 15 Jun 2020 08:04:05 GMT

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

Re: Cumulative count of active clients

stevedep — Mon, 15 Jun 2020 08:08:42 GMT

Welcome! Glad to help.