counting records before a specific date

[Anonymous]

I am very new to power Bi and DAX and need help. I have records of dates for when a account was created(Activated Date) for a user. The records go back to 2012. I am showing data for each month of 2022. And I want to show how many people were still active before the last date of each month. I dont want to count people who have a In-Activated date or who have Activated date after the respective month. right now the formula looks like 

CALCULATE(COUNTROWS('RegisteredUsers'[User Name]),'RegisteredUsers'[In-Activated Date]="NULL")

[amitchandak]

@Anonymous , I think You need something similar to this HR blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Or check the attached files

[Anonymous · Accepted solution]

Hi @Anonymous ,

Here I create a sample to have a test.

DimDate table:

DimDate = 
ADDCOLUMNS( CALENDAR(DATE(2022,01,01),DATE(2022,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthLongName",FORMAT([Date],"MMMM"))

Please try this code to create a measure to count the active user before each month ending date.

Measure:

Active User =
VAR _MAXDATE =
    MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        COUNT ( RegisteredUsers[User Name] ),
        FILTER (
            RegisteredUsers,
            RegisteredUsers[Activated Date] <= _MAXDATE
                && OR (
                    RegisteredUsers[In-Activated Date] = BLANK (),
                    RegisteredUsers[In-Activated Date] > _MAXDATE
                )
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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