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.
Hi
I'm working with a table a bit like this:
User Logins
ID | Timestamp |
1 | 21/09/2022 00:00:00 |
4 | 20/09/2022 00:00:00 |
1 | 20/09/2022 00:00:00 |
... | ... |
I have chosen to categorize a group of users as "Active Users", as the ones which has been logged in the past 14 days.
So, I can retrieve all my "Active Users" by using this Measure:
But - here's my problem. I want to create a graph of "Active Users" for the past 7 days.
So I need to somehow replace "TODAY()" with "Whatever X-day is showed on the x-axis on the graph".
Just to make it more clear, maybe we have the following results of the count:
Count of unique ID's | Date |
10 | 21/09/2022 |
15 | 20/09/2022 |
5 | 19/09/2022 |
7 | 18/... |
4 | 17/... |
7 | 16/... |
Then I would like to get the following result (just displayed in a graph instead):
Count of unique ID's the past 14 days | Date |
82 (10+15+5+7+4+7+...) | 21/09/2022 |
89 (15+5+7+4+7+...) | 20/09/2022 |
75 (5+7+4+7+...) | 19/09/2022 |
78 (7+4+7+...) | 18/09/2022 |
I hope it makes sense and that someone knows the necessary formula! 🙂
/Vlad
Solved! Go to Solution.
For others - I've found my own solution for this.
I created a new table with the following settings:
Active Users Past 7 Days = FILTER(Dates, Dates[Date] > Today() - 7 && Dates[Date] <= Today())
Active Users =
VAR Selected = 'Active Users Past 7 Days'[Date]
RETURN
CALCULATE(DISTINCTCOUNT('User Logins'[UserId]), 'User Logins'[timestamp] > Selected-14)
This results in a table with two columns. One being the past 7 days, another being the sum og distinct user id's for the past 14 days per day.
For others - I've found my own solution for this.
I created a new table with the following settings:
Active Users Past 7 Days = FILTER(Dates, Dates[Date] > Today() - 7 && Dates[Date] <= Today())
Active Users =
VAR Selected = 'Active Users Past 7 Days'[Date]
RETURN
CALCULATE(DISTINCTCOUNT('User Logins'[UserId]), 'User Logins'[timestamp] > Selected-14)
This results in a table with two columns. One being the past 7 days, another being the sum og distinct user id's for the past 14 days per day.
@Vlad_M_ Maybe:
Measure =
VAR __Date = MAX('Table'[Date]) //whatever is used in the x-axis
VAR __MinDate = __Date - 14
RETURN
SUMX(FILTER(ALL('Table'),[Date] >= __MinDate && [Date] <= __Date),[Count of Unique ID's])
Hey Greg
For some reason I couldn't get it to work the way I hoped, but thanks for the input 🙂
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |