Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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!
@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!
@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])))
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.
@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...
User | Count |
---|---|
50 | |
39 | |
18 | |
14 | |
13 |
User | Count |
---|---|
99 | |
71 | |
29 | |
18 | |
13 |