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

Seeing gained clients instead of total

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.

 

Screenshot (8).png

 

Thanks for the help in advance.

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

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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

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

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors