cancel
Showing results for
Did you mean:
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.

Thanks for the help in advance.

1 ACCEPTED SOLUTION
Super User

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.

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

Helper I

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
Super User

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.

Announcements

#### The Power BI Community Show

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

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