How to Determine New Client Accounts This Year

[kennedy311]

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!

[kennedy311]

@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!

[amitchandak]

@kennedy311 , 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])))

[kennedy311]

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.

[amitchandak]

@kennedy311 , 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-Retentio...

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...