Forum Discussion

Vytautas's avatar
Vytautas
Frequent Visitor
5 years ago
Solved

Calculate active users based for specific date

Hello,

 

I have a table of users with their IDs, startdate, enddate, locationID.

If user is still active - End date is date of today.

I need to calculate daily, monthly and yearly number of active users. 

 
By using this DAX I get correct number of daily users, however monthly and yearly totals are wrong (it supposed to be average of all days)
 
Active users =
CALCULATE(
COUNTROWS(active_users),
FILTER(active_users, (active_users[users_start] <= LASTDATE(D_TimeDim[Date])
&& active_users[end_date]>= FIRSTDATE(D_TimeDim[Date]))))

 

 

p.s. in this case I use 2 tables with no relationships between them. One table is users table, another one - Dates table. 

 

Any ideas how to fix it? 

Thank you

V.

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Vytautas 

     

    create a measure like this:

    _Active users =
CALCULATE (
    DISTINCTCOUNT ( Active_users[id] ),
    FILTER (
        Active_users,
        [start] <= MAX ( 'D_TimeDim'[Date] )
            && [end_date] >= MAX ( 'D_TimeDim'[Date] )
    )
)

     The total will show the number of active users on the last day of the month.

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Vytautas ,

     

    I'm not sure if there is an error in the logic.

    Shouldn't the start date be bigger than the first date and the end date smaller than the last date?

     

    Can you try the following measure:

    Active users =
CALCULATE(
    COUNTROWS( active_users ),
    FILTER(
        active_users,
        (
            active_users[users_start] >= FIRSTDATE( D_TimeDim[Date] )
            && active_users[end_date] <= LASTDATE( D_TimeDim[Date] )
        )
    )
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Vytautas's avatar
      Vytautas
      Frequent Visitor
      5 years ago

      Hey selimovd, thanks for replying. 

      Nop, your solutions does not work. I tried it and there is no values at all 😕 

       

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        Hey Vytautas ,

         

        in general I would use MIN and MAX instead of FIRSTDATE and LASTDATE. See the article of SQLBI for further details:

        Understanding the difference between LASTDATE and MAX in DAX - SQLBI

         

        Try the formula without the FILTER function:

        Active users =
CALCULATE(
    COUNTROWS( active_users ),
    active_users[users_start] >= MIN( D_TimeDim[Date] )
    && active_users[end_date] <= MAX( D_TimeDim[Date] )
)

         

        If that doesn't work, would it be possible to share your file or a similar demo file?

        That's a lot easier than the post-ping-pong

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis
         

         

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, Vytautas 

     

    create a measure like this:

    _Active users =
CALCULATE (
    DISTINCTCOUNT ( Active_users[id] ),
    FILTER (
        Active_users,
        [start] <= MAX ( 'D_TimeDim'[Date] )
            && [end_date] >= MAX ( 'D_TimeDim'[Date] )
    )
)

     The total will show the number of active users on the last day of the month.

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.