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 ,

 

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