topic Re: How to Determine New Client Accounts This Year in DAX Commands and Tips

How to Determine New Client Accounts This Year

Anonymous — Thu, 03 Mar 2022 23:30:52 GMT

I am trying to come up with a count of new Accounts (or clients) we have serviced this year. I would like it to be dynamic, so that when we roll over to 2023, the number resets to zero.

I have two tables in this problem: an Accounts table (where my clients are stored) and a Placements table (a fact table of services we have provided, at the transaction level). Snippet of my data model:

Ultimately, I need to look into the Placements table and find placements that:

were done for an Account for the very first time, and
done this year

I would like to be able to both count the number of Accounts where we did placements for this first time, AS WELL AS list those Accounts in a visualization.

I'm in over my head on this, DAX-wise, so if I'm missing some information here, let me know and I'll be happy to oblige. I am hopig this is relatively simple, but we'll see. Thank you in advance!

Re: How to Determine New Client Accounts This Year

Anonymous — Thu, 03 Mar 2022 23:47:20 GMT

One correction here...the Accounts table contains clients AND prospects, so a company could be in the Accounts table without ever appearing in the Placements table.

Re: How to Determine New Client Accounts This Year

amitchandak — Fri, 04 Mar 2022 03:22:50 GMT

Anonymous , We call account new when we have done a placement this year and no placement before that ? or based on account creation date ?

Using a date or year table

//Only year vs Year, not a level below

This Year = CALCULATE(placement[account id]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(placement[account id]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

for below year leve

this year = count(placement[account id])

last year = CALCULATE(placement[account id])dateadd('Date'[Date],-1,Year))

new = countx(values(account[account id]), if(not(isblank([this year])) && isbalnk([last year]) , [account id], blank()) )

refer

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

Re: How to Determine New Client Accounts This Year

Anonymous — Fri, 04 Mar 2022 15:29:42 GMT

amitchandakI found a solution, but I had to look at the data in a completely different way (and in an entirely different table than what I posted above).

I did try your This Year measure, but it kept saying that there was a syntax error that I could not resolve. Just a heads up there. Thank you for the response...I've seen you on here a lot and you are always very knowledgable!

Re: How to Determine New Client Accounts This Year

amitchandak — Fri, 04 Mar 2022 15:32:48 GMT

Anonymous , Thanks for the update. Sorry for the mistake, I forget to put aggregation

This Year = CALCULATE(count(placement[account id]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))