Determine if a name appears on a list between date1 and date2

[nmurphy2]

I have a file with the Names and the Dates where those names appeared on a particular list.

I need to know if between two dates, a new name appeared on the list.

I need to know the number that entered the list and the approximate % of change.

I then need to use that to calculate turnover based on the closest report date to the 15th of the month.

List of names/dates

Name	Date
Name1	12/13/2019
Name1	1/17/2020
Name1	2/19/2020
Name2	12/13/2019
Name3	2/19/2020
Name4	12/13/2019
Name4	1/17/2020
Name4	2/19/2020
Name4	3/13/2020
Name4	3/17/2020
Name4	3/18/2020
Name4	3/19/2020
Name4	3/20/2020
Name4	3/23/2020
Name4	3/24/2020
Name4	3/25/2020
Name4	3/26/2020
Name4	3/30/2020
Name4	4/2/2020
Name4	4/6/2020
Name4	4/7/2020
Name4	4/14/2020
Name4	4/21/2020
Name4	4/22/2020
Name4	4/27/2020
Name4	4/29/2020
Name4	4/30/2020
Name4	5/8/2020
Name4	5/11/2020
Name4	5/12/2020
Name4	5/13/2020
Name4	5/19/2020
Name4	5/21/2020
Name4	6/4/2020
Name4	6/8/2020
Name4	6/15/2020
Name4	6/17/2020
Name4	6/18/2020
Name4	6/22/2020
Name4	6/23/2020
Name5	1/17/2020
Name6	6/4/2020
Name6	6/8/2020
Name6	6/15/2020
Name6	6/17/2020
Name6	6/18/2020
Name6	6/22/2020
Name6	6/23/2020
Name7	3/13/2020
Name7	3/17/2020
Name7	3/18/2020
Name7	3/19/2020
Name7	3/20/2020
Name7	3/23/2020
Name7	3/24/2020
Name7	3/25/2020
Name7	3/26/2020
Name7	3/30/2020
Name8	2/19/2020
Name9	12/13/2019
Name9	1/17/2020
Name9	2/19/2020
Name9	4/27/2020
Name9	4/29/2020
Name9	4/30/2020
Name9	5/8/2020
Name9	5/11/2020
Name9	5/12/2020
Name9	5/13/2020
Name9	5/19/2020
Name9	5/21/2020

Desired Result

Date	Total Added	Approximate Turnover
3/13/2020	1	11%
4/14/2020	0	0%
5/13/2020	0	0%
6/15/2020	0	0%

[v-diye-msft]

Hi @nmurphy2 

if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[nmurphy2]

Half way there.

Report Date gives me the desired report dates.

But what I need for each of those report dates is the # of names that entered (entered only; not entered or left) the list.

Here's a how I determined the #'s in Excel

Was it "on the list" on the date indicated? Blank = no 1=yes	Date
Name	12/13/2019	1/17/2020	2/19/2020	3/13/2020	4/14/2020	5/13/2020	6/15/2020
Name1	1	1	1
Name2	1
Name3			1
Name4	1	1	1	1	1	1	1
Name5		1
Name6							1
Name7				1
Name8			1
Name9	1	1	1			1
Did it "enter the list" on the date indicated 0 = no 1 = yes
	Date
Name	12/13/2019	1/17/2020	2/19/2020	3/13/2020	4/14/2020	5/13/2020	6/15/2020
Name1		0	0	0	0	0	0
Name2		0	0	0	0	0	0
Name3		0	1	0	0	0	0
Name4		0	0	0	0	0	0
Name5		1	0	0	0	0	0
Name6		0	0	0	0	0	1
Name7		0	0	1	0	0	0
Name8		0	1	0	0	0	0
Name9		0	0	0	0	1	0
# added		1	2	0	0	0	0
% of total		11%	22%	0%	0%	0%	0%

[mahoneypat]

In your earlier post, I thought you need report date to be the closest to the 15th w/o going over.  However, I see you just need the closest one.  Also, I see you are using the total # of names for your denominator (instead of the people there at that time).  Here is a revised Report Date column expression and and updated Turnover measure that gets your expected results.

Report Date 2 =
VAR yearmonth =
    FORMAT ( Names[Date], "YYYYMM" )
VAR thismonthtable =
    FILTER ( Names, FORMAT ( Names[Date], "YYYYMM" ) = yearmonth )
VAR daysfrom15 =
    ADDCOLUMNS ( thismonthtable, "@daysfrom15", ABS ( DAY ( Names[Date] ) - 15 ) )
VAR top1 =
    TOPN ( 1, daysfrom15, [@daysfrom15], ASC )
VAR getdate =
    MAXX ( top1, Names[Date] )
RETURN
    getdate


Turnover = 
VAR currentnames =
    VALUES ( Names[Name] )
VAR thisreportdate =
    MIN ( Names[Report Date 2] )
VAR pastnames =
    CALCULATETABLE (
        VALUES ( Names[Name] ),
        ALL ( Names ),
        Names[Date] < thisreportdate
    )
var allnames = ALL(Names[Name])
RETURN
    DIVIDE (
        COUNTROWS ( EXCEPT ( currentnames, pastnames ) ),
        COUNTROWS ( allnames )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nmurphy2]

In order to get the rows where the % is zero, I also added in

New measure
turnover2 = if(ISBLANK([Turnover]),0,[Turnover])

[nmurphy2]

Thanks for the latest attempt.  It is closer to what I need.

What I see when I run the code is this:

What I need to see is this

1/17/2020      .11

2/19/2020      .22

3/13/2020      .11

4/14/2020      .00

5/13/2020      .11

6/15/2020      .11

Since the first date is 12/31/2019, there is no "previous" month to compare it to, so it should not appear.

[mahoneypat]

I believe this expression gets your desired result, but not sure I understand your calculation.  First thing I did was to make a report date column with this expression, which calculates the date closest to the 15th of each month.

Report Date =
VAR thismonth15 =
DATE ( YEAR ( Names[Date] ), MONTH ( Names[Date] ), 15 )
VAR reportdate =
CALCULATE ( MAX ( Names[Date] ), ALL ( Names ), Names[Date] <= thismonth15 )
RETURN
reportdate

You can then use that new column in a table visual with this measure to get close to your desired result.  I say close to as I got 14% for 3/13, not 11%.  At that time, there are 7 names in the list (1/7 = 14%).  What is the calculation you are doing to get 11%?  In any case, maybe this approach gets you close and you can adapt it.

Turnover =
VAR currentnames =
    VALUES ( Names[Name] )
VAR thisreportdate =
    MIN ( Names[Report Date] )
VAR pastnames =
    CALCULATETABLE (
        VALUES ( Names[Name] ),
        ALL ( Names ),
        Names[Date] < thisreportdate
    )
RETURN
    DIVIDE (
        COUNTROWS ( EXCEPT ( currentnames, pastnames ) ),
        COUNTROWS ( pastnames )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat