Forum Discussion

RickPowerBI's avatar
RickPowerBI
Icon for Helper I rankHelper I
6 years ago

Need help CUMULATIVE active clients using start/end date

Hi all,

I have posted this question before but after some research and tips from the community I can explain the problem better.

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.
So I need a formula that calculates when a client is active by looking at start agreement and end agreement. Now I depend on the agreement state column.
So the question is: Which Syntax(es) should I be looking at?

Because I know what needs to happen but I don't know how to do it.

 

This is the formula I have now:

Active clients =
CALCULATE(
    COUNT('Clients'[Client Code]),
    FILTER('Clients', 'Clients'[Price Agreement State] = "active"))

 

Sample data:

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

 

Thanks for the help in advance!