Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to 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.
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.
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] )
)
)
)
Hey @selimovd, thanks for replying.
Nop, your solutions does not work. I tried it and there is no values at all 😕
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |