Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I do not have access to transform the data and must use a measure to meet the following needs.
I have a table called 'Activity Details' with an ID column called [Solution User ID] and a date column called [Event Date]. There are currently around 800k rows.
Every time the user performs an action, there is a row created in this table. I need to calculate how many users were active in the last 12 months, given a particular date, and also put this on a line chart.
The below measure works, but is extremely slow. When analysing in DAX studio, I notice that for every date, it runs a Storage Engine query. I tried a combination of SUMX and SUMMARIZE formula as well, and while that reduced the number of SE queries, it was still about the same speed due to processing a high number of rows.
Active Users =
var latestDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('Activity Details'[Solution User ID]),
ALL('Calendar'[Date]),
'Activity Details'[Event Date] <= latestDate,
'Activity Details'[Event Date] >= latestDate - 365
)
Would appreciate if anyone can suggest a better formula.
Hi,
Does this work faster?
=calculate(DISTINCTCOUNT('Activity Details'[Solution User ID]),datesbetween(calendar[date],max(calendar[date])-365,max(calendar[date])))
The fully expanded version of the formula you wrote is as follows
Active Users =
VAR latestDate =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Activity Details'[Solution User ID] ),
ALL ( 'Calendar'[Date] ),
FILTER (
ALL ( 'Activity Details'[Event Date] ),
'Activity Details'[Event Date] <= latestDate
),
FILTER (
ALL ( 'Activity Details'[Event Date] ),
'Activity Details'[Event Date] >= latestDate - 365
)
)
what you need is this
Active Users =
VAR latestDate =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Activity Details'[Solution User ID] ),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= latestDate
&& 'Calendar'[Date] >= latestDate - 365
)
)
Hey @MattAllington Thanks for your reply. I am afraid it didn't work though.
Firstly, the 'Calendar' table is just a date table and it is not related to any other table as I am only using it as a 'To Date' filter in my metrics. All the metrics sum or count since the beginning of time or a fixed (today - 365 days) period.
Even if I remove ALL ( 'Calendar'[Date] ) from my original formula or adjust yours as below, it still executes roughly the same number of Storage Execution queries as the number of dates in the filter.
Active Users =
VAR latestDate =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Activity Details'[Solution User ID] ),
FILTER (
'Activity Details',
'Activity Details'[Event Date] <= latestDate
&& 'Activity Details'[Event Date] >= latestDate - 365
)
)
Well, if there is no relationship, then yes, it won't work. Generally speaking, you should not filter a table if you can filter a column. So the first parameter of filter is better as ALL('Activity Details'[Event Date]). But better still is to have a calendar table with a relationship.
@MattAllington I uploaded a very simplified version of the model here. Please suggest what changes you'd do to make it work. Basically, I just have a Date table to use in filtering and to use as the X Axis in charts. The discount count needs to be done from 365 days prior to the date on the X Axis.
Hi,
See the second tab of this PBI file.
I am afraid this DAX also calls one storage engine query per date. Also, we need to filter by 'Event Date' not Calendar[Date] as there is no relationship.
The following query only calls the storage engine once, but has a long wait in formula engine.
Active Users =
var latestDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUMX(
SUMMARIZE(
'Activity Details', 'Activity Details'[Group User ID],
"Last Event", MAX('Activity Details'[Event Date])
),
1
),
ALL('Calendar'[Date]),
'Activity Details'[Event Date] <= latestDate,
'Activity Details'[Event Date] >= (latestDate - 365)
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
27 |
User | Count |
---|---|
92 | |
50 | |
44 | |
40 | |
35 |