Determine a new person, a partially new person, and not a new person?

[D_PBI]

Hi,
The below drop-box contains a .PBIX file that contains the data and model I'm working with.
https://www.dropbox.com/scl/fi/oprbzwlwuhaep5ilsfd62/NewVSPreviousVSNotNew.pbix?rlkey=dxr509tlsvmk5l...

The model has the __dimDate_DateOfFirstContact table (which is built using a custom financial year) that filters (on a one to many basis) the Disclosures table.

Each Leadid will only have one Date of first Contact.
Each Leadid will only have one Case Lead id.
Each Leadid can have more than one Participant id.
Each Case Lead id should appear as a Participant for the Leadid, however there may be further Participants present for the same Leadid.

Based on the end-user selecting a Year (in this example 2019-20 which 01/08/2019-31/07/2020), I need to produce the following calculations:
1) New
For all Leadid values in the selected year (2019-20), it will produce a list of Case Lead id values. For each Case Lead id that is not present in the dataset prior to the year 2019-20, whether as a Case Lead or as a Participant, then that Case Lead is classed a 'New' and counted in this measure - and if counted in the measure, the Case Lead id value should not be counted in the other two measures.

2) Participant
For all Leadid values in the selected year (2019-20), it will produce a list of Case Lead id values. For each Case Lead id that is not present in the dataset prior to the year 2019-20 as a Case Lead, but is present prior to the year 2019-20 in the Participant id list, then the Case Lead is classed as a 'Participant' and counted in this measure - and if counted in the measure, the Case Lead id value should not be counted in the other two measures.

2) Not New
For all Leadid values in the selected year (2019-20), it will produce a list of Case Lead id values. For each Case Lead id that is present in the dataset prior to the year 2019-20 as a Case Lead, then the Case Lead is classed as a 'Not New' and counted in this measure.

Should the end-user select more than one year, so say 2019-20 and 2022-23, then the same three measures should work out the calculations for each selected year as the intention is to show the same calculations per year selected. So if one year was selected we'd have three bars, one for each measure. If two years were selected we'd have two lots of three bars - three bars for the first year, then separately three bars for the second year all in the same visual.

When using the .PBIX attached and choosing the Financial Year of 2019-20 (which covers the date period of 01/08/2019-31/07/2020), it returns the following Case Leads:
Ivom Blue
Yovior Orange
Rojosl Navy
Ilommis Royal
Yom Grey

When focusing on these Case Leads and viewing their activity in the dataset, within the .PBIX file, the New, Participants, and Not new calculations should result in the following:
New:
Ivom Blue (the earliest date as Case Lead is 16/11/2019, the earliest date as Participant is 16/11/2019) . As the earliest present is within the selected year, Ivom Blue is counted as New.
Rojosl Navy (the earliest date as Case Lead is 17/06/2020, the earliest date as Participant is 20/06/2020) . As the earliest present is within the selected year, Rojosl Navy is counted as New.
Yom Grey (the earliest date as Case Lead is 05/02/2020, the earliest date as Participant is 05/02/2020) . As the earliest present is within the selected year, Yom Grey is counted as New.

Participant:
Ilommis Royal (the earliest date as Case Lead is 20/12/2019, the earliest date as Participant is 20/12/2018). As the earliest date present is before the selected year and being as a Participant, Ilommis Royal is counted as a Participant.

Not new:
Yovior Orange (the earliest date as Case Lead is 29/04/2019, the earliest date as Participant is 29/04/2019). As the earliest date present is as a Case Lead and this was prior to the selected year, Yovior Orange is counted as Not new.

So based on the dataset included in the .PBIX file and the logic explained, for the selected year of 2019/20 – New should be 3, Participant should be 1, and Not new should be 1.
As you can see from the screenshot at the top of this thread, my efforts (the same in the downloadable .PBIX file) are wrong.

Please can someone help me with this?  It's beating me, but I would think the high talent on this forum would be able to get this working?
Thank you.

[amitchandak]

@D_PBI , You can follow Customer retention approach given here

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

example measures

Lost Customer This Year = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([This Year]) && not(ISBLANK([Last Year])) , 1,BLANK()))
New Customer This Year = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([Last Year]) && not(ISBLANK([This Year])) ,1,BLANK()))
Retained Customer This Year = if(not(ISBLANK([This Year])) && not(ISBLANK([Last Year])) , 1,BLANK())

You can have this and last year's measure using the following

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

[v-xiandat-msft]

Hi @D_PBI ,

Sorry, I can't open your pbix file, it's blank!

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D_PBI]

Hi @v-xiandat-msft I've not received an update to if you can now see/download the .PBIX file. It is accessable and I've tested it through peers. Please can you take another look?
Is there another method where I can share this .PBIX file with you?
I really need to get these calculations working and I'm struggling to achieve that.

[D_PBI]

@v-xiandat-msft - it should be downloadable. I've taken a fresh copy of the URL and paste it below.
https://www.dropbox.com/scl/fi/oprbzwlwuhaep5ilsfd62/NewVSPreviousVSNotNew.pbix?rlkey=dxr509tlsvmk5l...

You should see the above screen where you will need to click on the 'Download' button. I know it does work for others, so not sure why it doesn't work for you. Please can you try again.