Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

counting records before a specific date

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

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.