Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count of rolling count

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!

  • johnt75's avatar
    johnt75
    4 years ago
    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. 

16 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago
    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.