Calculate active users based for specific date

[Vytautas]

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.

[v-angzheng-msft · Accepted solution]

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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Vytautas]

Hey @selimovd, thanks for replying. 

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

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Vytautas]

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