topic Re: counting records before a specific date in DAX Commands and Tips

counting records before a specific date

Anonymous — Wed, 13 Jul 2022 13:25:40 GMT

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")

Re: counting records before a specific date

amitchandak — Wed, 13 Jul 2022 13:53:02 GMT

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

Re: counting records before a specific date

Anonymous — Mon, 18 Jul 2022 08:47:04 GMT

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.