Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
johan-svitla
Regular Visitor

Counting active users, joiners & leavers

Hello everyone, I'm relatively new to PowerBI, and I've done a fair amount of research and tried different approaches without success. The idea is to show how many people joined the company in a particular month, how many left, and the number of active users in a specific month/year.

I have a table with user information with columns id, start date, and end date, and I want to create a clustered column chart with three categories:
- users with a start date in the current month named "Joiners".
- users with an end date in the current month named "Leavers".
- users with a start date before the current month and an end date blank named "Active".

 

Data looks like this, when an user is still active, then End Date is blank.

ID                    Start Date                 End Date

1312313           01/23/2020              12/24/2021 
1231231           08/15/2021              09/01/2022
2534556           12/20/2022                      
4356546           01/01/2023               04/20/2023

2534556           10/12/2021
4356546           02/13/2020               


This is an example of what I'm trying to get. Would you help me with that or point me in the right direction? Any help will be highly appreciated.

 

johansvitla_0-1685382299303.png

 

1 ACCEPTED SOLUTION

Just i case someone is looking for the answer, I found it here:

 

https://youtu.be/e6Y-l_JtCq4

View solution in original post

6 REPLIES 6
NaveenGandhi
Super User
Super User

Hello @johan-svitla 

How would you define current month, Will you be using a slicer? 

Also if start_date & end_date falls on current month, What would be the status.

 

Regards,

Naveen

Hi. Yes, the slicer would be my choice. If someone joined and left in the same month should be counted in both joiners and leavers.

 

Regards and thanks for your interest

@johan-svitla 

Id 4356546  has two entries, can you let me know what should be the desired output in such cases?

Hi, this is a mistake on the sample data. IDs are unique and are never duplicated under any circumstance

@johan-svitla 

 

Please try the below solution, This uses a combination of PQ and DAX.

Unpivot your table to below structure, as this is important to visuals leavers along with other two metric.

NaveenGandhi_1-1685468668688.png

 

Create below three measures, Use a disconnected calendar table for slicer and alter the measure accordingly.

 

Joiners =
CALCULATE (
COUNT ( Active[ID ] ),
FILTER (
Active,
IF (
AND (
MAX ( 'Calendar'[Month] ) = MONTH ( MAX ( Active[Date] ) ),
MAX ( 'Calendar'[year] ) = YEAR ( MAX ( Active[Date] ) )
),
1,
0
) = 1
),
Active[Type] = "Start Date"
)

Leavers =
CALCULATE (
COUNT ( Active[ID ] ),
FILTER (
Active,
IF (
AND (
MAX ( 'calendar'[Month] ) = MONTH ( MAX ( Active[Date] ) ),
MAX ( 'Calendar'[year] ) = YEAR ( MAX ( Active[Date] ) )
),
1,
0
) = 1
),
Active[Type] = "End Date"
)

Active =
VAR value_ =
LOOKUPVALUE (
'Calendar'[Date],
'Calendar'[Date], SELECTEDVALUE ( Active[Date] )
)
RETURN
CALCULATE (
COUNT ( Active[ID ] ),
FILTER (
Active,
IF (
AND (
CALCULATE ( COUNTROWS ( Active ), ALLEXCEPT ( Active, Active[ID ] ) ) = 1,
MONTH ( value_ ) < SELECTEDVALUE ( 'Calendar'[Month] )
&& YEAR ( value_ ) <= SELECTEDVALUE ( 'Calendar'[Year] )
),
1,
0
) = 1
)
)

Now use the date column and these three measure to visualize as below.

NaveenGandhi_2-1685468864654.png

Let me know if this helps.

 

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

Just i case someone is looking for the answer, I found it here:

 

https://youtu.be/e6Y-l_JtCq4

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.