Calculating Remaining Active Joiners After Joining Month in Power BI

[DataAnalyst7]

Description:

Hello Power BI Community,

I'm seeking help with creating a DAX measure in Power BI to calculate the number of remaining active joiners each month after their joining month. I have two main tables: JoinersSummary and NewJoinerAndLeavers, both connected to a Calender table.

Objective:

• Determine the number of remaining active joiners in each subsequent month after they initially joined.

Data Structure:

• JoinersSummary table contains columns: SimNo, IsNewJoiner, Merged (joining date in "yyyy-mm" format).
• JoinerAndLeavers table contains columns: SimNo, Merged, ThreeMonthLeaver AND MORESample dataWhat I am trying to do

Current Issue: I have created a measure to calculate remaining joiners, but it currently returns the same count as the new joiners, rather than the count of those who are still active after the joining month. The goal is to have a measure that correctly identifies and counts the remaining active joiners for each month.                                                                                                                                   

Remaining Joiners Optimized = 
VAR SelectedMonth = MAX('JoinersSummary'[Merged])
VAR JoinersInSelectedMonth = 
    CALCULATETABLE(
        VALUES('JoinersSummary'[SimNo]),
        'JoinersSummary'[IsNewJoiner] = 1,
        'JoinersSummary'[Merged] = SelectedMonth,
        REMOVEFILTERS('NewJoinerAndLeavers')
    )
VAR RemainingJoinersCount = 
    CALCULATE(
        DISTINCTCOUNT('NewJoinerAndLeavers'[SimNo]),
        'NewJoinerAndLeavers'[SimNo] IN JoinersInSelectedMonth,
        'NewJoinerAndLeavers'[Merged] > SelectedMonth,
        REMOVEFILTERS('Calender')
    )
RETURN
RemainingJoinersCount

This measure returns the same count as the new joiners count, not reflecting the actual number of joiners remaining active after the joining month.

Additional Information:

• The JoinerSummary table . is made up of data from the NewJoinersandLeavers Table
• The Merged column is in the "yyyy-mm" format as a Date type.

Request: I need guidance on correctly calculating the remaining active joiners for each subsequent month after their joining month. Any insights or suggestions would be greatly appreciated.

Thank you for your help!

[Anonymous]

Hi @DataAnalyst7 

It seems like the issue with your current DAX measure is that it's not accounting for leavers in the months following the joiners' initial month. Maybe you can try the following Dax:

Remaining Active Joiners = 
VAR SelectedMonth = MAX('JoinersSummary'[Merged])
VAR JoinersInSelectedMonth = 
    CALCULATETABLE(
        VALUES('JoinersSummary'[SimNo]),
        'JoinersSummary'[IsNewJoiner] = 1,
        'JoinersSummary'[Merged] = SelectedMonth
    )
VAR RemainingJoiners = 
    FILTER(
        JoinersInSelectedMonth,
        CALCULATE(
            COUNTROWS('NewJoinerAndLeavers'),
            'NewJoinerAndLeavers'[SimNo] = EARLIER('JoinersSummary'[SimNo]),
            'NewJoinerAndLeavers'[Merged] > SelectedMonth,
            'NewJoinerAndLeavers'[ThreeMonthLeaver] = 0
        ) > 0
    )
RETURN
COUNTROWS(RemainingJoiners)

Best Regards,

Jayleny

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

[DataAnalyst7]

hi @Anonymous Thank you for the respone. The solution you gave still produce the correct number of joiners but not the remaining joiners.

Look at the image.