Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
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!
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% |
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
To learn more about Power BI, follow me on Twitter or subscribe 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:
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |