Seeing gained clients instead of total

[RickPowerBI]

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.

[lbendlin]

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).

[RickPowerBI]

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

[lbendlin · Accepted 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.