Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hello everyone,
I have a table of active employees of each month like the following.
Date | Employee |
2020/12/1 | David |
2020/12/1 | Jeff |
2020/12/1 | Marco |
2020/12/1 | Joy |
2020/12/1 | Peter |
2021/1/1 | David |
2021/1/1 | Jeff |
2021/1/1 | Marco |
2021/1/1 | Joy |
2021/1/1 | Jenny |
2021/1/1 | Kate |
2021/2/1 | Jeff |
2021/2/1 | Marco |
2021/2/1 | Joy |
2021/2/1 | Jenny |
2021/2/1 | Kate |
2021/2/1 | Aaron |
From 2020/12 to 2021/1, 1 person Peter left, and 2 people Jenny and Kate joined. From 2021/1 to 2021/2, David left and Aaron joined.
But if we were to look from 2020/12 to 2021/2, Peter and David left, and Jenny, Kate and Aaron joined.
I want to be able to show how many people joined and how many people left, and also the turnover rate of a date range.
I am sorry but I cannot think of a good way to do this. Need some help on this, thank you!
Best regards,
David
Solved! Go to Solution.
Hi @primolee,
Try the following formula to create measures:
Left =
var _First = MIN('Table'[Date])
var _Last = MAX('Table'[Date])
var Table1 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_First)
&&'Table'[Date].[Year] = YEAR(_First)
),
"Employee",
'Table'[Employee]
)
var Table2 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_Last)
&&'Table'[Date].[Year] = YEAR(_Last)
),
"Employee",
'Table'[Employee]
)
return
COUNTAX(
EXCEPT(Table1,Table2),
[Employee]
)
Joined =
var _First = MIN('Table'[Date])
var _Last = MAX('Table'[Date])
var Table1 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_First)
&&'Table'[Date].[Year] = YEAR(_First)
),
"Employee",
'Table'[Employee]
)
var Table2 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_Last)
&&'Table'[Date].[Year] = YEAR(_Last)
),
"Employee",
'Table'[Employee]
)
return
COUNTAX(
EXCEPT(Table2,Table1),
[Employee]
)
This is my PBIX file.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @primolee,
Try the following formula to create measures:
Left =
var _First = MIN('Table'[Date])
var _Last = MAX('Table'[Date])
var Table1 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_First)
&&'Table'[Date].[Year] = YEAR(_First)
),
"Employee",
'Table'[Employee]
)
var Table2 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_Last)
&&'Table'[Date].[Year] = YEAR(_Last)
),
"Employee",
'Table'[Employee]
)
return
COUNTAX(
EXCEPT(Table1,Table2),
[Employee]
)
Joined =
var _First = MIN('Table'[Date])
var _Last = MAX('Table'[Date])
var Table1 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_First)
&&'Table'[Date].[Year] = YEAR(_First)
),
"Employee",
'Table'[Employee]
)
var Table2 =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Date].[MonthNo] = MONTH(_Last)
&&'Table'[Date].[Year] = YEAR(_Last)
),
"Employee",
'Table'[Employee]
)
return
COUNTAX(
EXCEPT(Table2,Table1),
[Employee]
)
This is my PBIX file.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@primolee , is this like a snapshot table?
Check the approach of Month on Month using isblank for Customer Retention, that should help
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...