cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmurphy2
Helper I
Helper I

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

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

NameDate
Name112/13/2019
Name11/17/2020
Name12/19/2020
Name212/13/2019
Name32/19/2020
Name412/13/2019
Name41/17/2020
Name42/19/2020
Name43/13/2020
Name43/17/2020
Name43/18/2020
Name43/19/2020
Name43/20/2020
Name43/23/2020
Name43/24/2020
Name43/25/2020
Name43/26/2020
Name43/30/2020
Name44/2/2020
Name44/6/2020
Name44/7/2020
Name44/14/2020
Name44/21/2020
Name44/22/2020
Name44/27/2020
Name44/29/2020
Name44/30/2020
Name45/8/2020
Name45/11/2020
Name45/12/2020
Name45/13/2020
Name45/19/2020
Name45/21/2020
Name46/4/2020
Name46/8/2020
Name46/15/2020
Name46/17/2020
Name46/18/2020
Name46/22/2020
Name46/23/2020
Name51/17/2020
Name66/4/2020
Name66/8/2020
Name66/15/2020
Name66/17/2020
Name66/18/2020
Name66/22/2020
Name66/23/2020
Name73/13/2020
Name73/17/2020
Name73/18/2020
Name73/19/2020
Name73/20/2020
Name73/23/2020
Name73/24/2020
Name73/25/2020
Name73/26/2020
Name73/30/2020
Name82/19/2020
Name912/13/2019
Name91/17/2020
Name92/19/2020
Name94/27/2020
Name94/29/2020
Name94/30/2020
Name95/8/2020
Name95/11/2020
Name95/12/2020
Name95/13/2020
Name95/19/2020
Name95/21/2020

 

Desired Result

DateTotal AddedApproximate Turnover
3/13/2020111%
4/14/202000%
5/13/202000%
6/15/202000%
6 REPLIES 6
v-diye-msft
Community Support
Community Support

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!

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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     
Name12/13/20191/17/20202/19/20203/13/20204/14/20205/13/20206/15/2020
Name1111    
Name21      
Name3  1    
Name41111111
Name5 1     
Name6      1
Name7   1   
Name8  1    
Name9111  1 
        
Did it "enter the list" on the date indicated
0 = no
1 = yes
       
 Date     
Name12/13/20191/17/20202/19/20203/13/20204/14/20205/13/20206/15/2020
Name1 000000
Name2 000000
Name3 010000
Name4 000000
Name5 100000
Name6 000001
Name7 001000
Name8 010000
Name9 000010
# added 120000
% of total 11%22%0%0%0%0%

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

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

What I see when I run the code is this:

nmurphy2_0-1595273969898.png

 

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!