Count of rolling count

[Anonymous]

Dear all,

I hope you are doing well.

Could you please help me with the following matter? I am pretty new to PowerBI and I am struggling with it.

I have two tables:

- A visitor table, there is one row per visitor connected to the platform per day. For instance, if the timeperiod is of 30 days, there can be up to 30 rows for each visitor.

- A calendar table, with all the dates of the visitor table.

I have the following, the number of frequent vs engaged visitors per month.

I would want the same thing but on a 30-day rolling basis. The graph I am looking for is the number of frequent and engaged visitors for everyday, knowing that the data each day represents the data for the count for the 30 days prior to that day.

I thought of separating frequent and engaged into two different measures. Here would be the pseudo code for the frequent users:

Measure = var currentDate = SELECTEDVALUE('calendar'[Date])

var date30DaysAgo = FIRSTDATE(DATEADD('calendar'[Date], -30, DAY))

RETURN

FILTER(SUMMARIZE(FILTER('Visitors',

'Visitors'[Date] <=currentDate &&

'Visitors'[Date] > date30DaysAgo

), "COUNT", COUNT('Visitors'[ID])), COUNT < 15)

I still cannot get my hands on the correct formula.

I am at your disposal should you have any questions.

Thank you in advance for your help!

[Anonymous]

Thank you both for your help.

I managed to put together that formula:

test =

var currentDate = SELECTEDVALUE('calendar'[Date])

var date30DaysAgo = FIRSTDATE(DATEADD('calendar'[Date], -30, DAY))

RETURN COUNTROWS(FILTER(

SUMMARIZE(

FILTER(

Visitors,

'Visitors'[Date] <= currentDate &&

'Visitors'[Date] > date30DaysAgo

), Visitors[Visitor ID], "COUNT", COUNT(Visitors[Visitor ID])

), [COUNT] = 1

)

)

It works but it calculates the number of active visitors everyday. I need the number of visitors for the last 30 days. I do not know where my error comes from.

[tamerj1]

@Anonymous 

Would you please define Frequent and Engaged visitors? Can you share the existing measures you have? Are these two measures working fine?

[Anonymous]

Thank you for your answer.

You can use whatever you want for Frequent and Engaged. My problem is to:

1) Count the Frequent and Engaged on a 30 day rolling basis;

2) Then count the previous count.

Thank you again!

[tamerj1]

Good morning @Anonymous 

You may try the following

Moving Total Measure =
VAR PeriodNumOfDays = 30
VAR FirstDateInFilter =
    MIN ( 'calendar'[Date] )
VAR LastDateInFilter =
    MAX ( 'calendar'[Date] )
VAR DatePeriodTable =
    DATESINPERIOD ( 'calendar'[Date], LastDateInFilter, - PeriodNumOfDays, DAY )
VAR LastDateWithData =
    MAX ( 'Visitors'[Date] )
VAR Result =
    CALCULATE ( [Measure], DatePeriodTable )
RETURN
    IF ( FirstDateInFilter <= LastDateWithData, Result )

[tamerj1]

Hi @Anonymous 

Have you tried this solution?

[johnt75 · Accepted solution]

Frequent users =
var maxDate = MAX('calendar'[Date])
var minDate = maxDate - 30
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Visitors', 'Visitors'[ID]), "@num visits", CALCULATE( COUNTROWS('Visitors'),
    REMOVEFILTERS('calendar'), 'Visitors'[Date] >= minDate && 'Visitors'[Date] <= maxDate ) )
return COUNTROWS( FILTER( summaryTable, [@num visits] >= 5 & [@num visits] <= 15 ) )

would return the number of visitors with between 5 and 15 visits in the preceding 30 days. 

[Anonymous]

Thank you for your response Johnt75.

Would you know how to do it on a rolling basis? I need to calculate it by/for every 30 days period.

Thank you in advance.

[johnt75]

This will work on a rolling basis, if you use Year and Month from your calendar table on the axis of a line chart, for example, then it will work out the 30 day figure as at the end of each month

[Anonymous]

Hi johnt75,

Thank you again for your help.

I tested again your formula. It works well in the sense it is possible to filter the count of logins for the previous 30 days for each date but the number of visitors displayed on the plot is the number of users of the day not of the month.

To give a more precise example: it seems that on February 28th it filters the visitors of the previous 30 days but only out the visitors of the 28th.

Please tell me if you have any questions and thank you again for your help!

[johnt75]

I think I understand. You want to figure for February to be the total of each day in February added together.

Rename my original measure as Frequent Users ( daily ) then try

Frequent Users ( monthly ) = SUMX( 
    ADDCOLUMNS( VALUES( 'Date'[Date]), "@daily", [Frequent Users ( daily )],
    [@daily]
)

[Anonymous]

Thank you for your answer!

Not exactly, I apologize for being unclear, I investigated a bit more in the meantime, to be more precise:

The problem with the first formula you shared, and I do not know why, is that everything is done for the current day and only for the current day. It does not take the 30 previous days in the calculations. So, currently, on the 28th of February, the num visits only filters the 28th of February. I would need it to filter the 30 days before the 28th of February.

I tried the Frequent Users Month but it does not change anything, I think there is an aggregate that does not work properly.

I cannot thank you enough for your help! I help I was a bit clearer

[johnt75]

OK, try

Frequent users ( monthly ) =
var currentDate = MAX('Date'[Date])
 RETURN SUMX( ADDCOLUMNS( DATESBETWEEN( 'Date'[Date], currentDate - 30, currentDate ),
   "@daily", [Frequent users ( daily )] ), [@daily] )

[Anonymous]

It produces some wrong numbers, I think that this calculation must be done at the summarize level in this measure:

Frequent users =
var maxDate = MAX('calendar'[Date])
var minDate = maxDate - 30
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Visitors', 'Visitors'[ID]), "@num visits", CALCULATE( COUNTROWS('Visitors'),
    REMOVEFILTERS('calendar'), 'Visitors'[Date] >= minDate && 'Visitors'[Date] <= maxDate ) )
return COUNTROWS( FILTER( summaryTable, [@num visits] >= 5 & [@num visits] <= 15 ) )

[Anonymous]

To develop my idea: I would need a summary table with the visitors id for the last 30 days and not for the day.

I am trying to do it on my side too!

[johnt75]

You could try wrapping the summary table in a GENERATE, like

var summaryTable = GENERATE( DATESBETWEEN( 'Date'[Date], minDate, maxDate), use existing summary definition here )

that should give a table which is effectively 1 summary table for each day in the preceding 30 days

[Anonymous]

Actually, your very first formula was what I wanted. I cannot thank you enough. I did not think of using add columns and it is what I needed.

Have a great week (soon weekend)!