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 activ...
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.

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
     

     

    • Vytautas's avatar
      Vytautas
      Frequent Visitor
      5 years ago

      Hi, thanks for reply.

      I have attached file. I should do that earlier, sorry.

      Your solution above does not work either, it says "A function 'MIN' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

      File link: https://we.tl/t-ei1onKVQ0N