Hello,
I have a problem with a task where I need to count number of users who did a task last year at least 12 times (not including 12) who are called heavy active users. My current formula looks like this:
Heavy Active Users =
VAR tableVar =
ADDCOLUMNS
(
FILTER
(
SUMMARIZE
(
Conversions, Conversions[user_pseudo_id], "Count records", COUNT
(
Conversions[user_pseudo_id]
)
), [Count records] > 12
), "aCol", 1
)
VAR SumUser = SUMX
(
tableVar, [aCol]
)
VAR Res = IF
(
ISBLANK
(
CALCULATE
(
SumUser, DATESINPERIOD
(
'Calendar'[Date], DATEADD
(
LASTDATE
(
Conversions[event_timestamp - Full Date]
), -364, DAY
),
365, DAY)
)
) = TRUE, CALCULATE
(
SumUser, DATESINPERIOD
(
'Calendar'[Date], DATEADD
(
LASTDATE
(
Conversions[event_timestamp - Full Date]
), -363, DAY
),
364, DAY
)
), CALCULATE
(
SumUser, DATESINPERIOD
(
'Calendar'[Date], DATEADD
(
LASTDATE
(
Conversions[event_timestamp - Full Date]
),
-364, DAY
),
365, DAY
)
)
)
RETURN Res
The IF function is used because one day there was no request and it caused negative values in the chart for another measure. The problem is that this formula only counts users who made a request in the current week (as in the column in the chart). I need to count all users who sent more than 12 requests in the whole last year, which means if there was a user who sent 13 requests 11 months ago and no other since then, he must be included in the heavy active users.
I got a recommendation to use WINDOW function instead but I got no results. Do you have any ideas?
