Rolling 4 week average conversion rate, in R or Power BI-CodePudding

I am trying to create a rolling 4 week average conversion rate. The column LTA is the conversion rate and equals (Appts/Leads). Right now, LTA is week by week. I need to create a new column that is a 4 rolling conversion rate. Here is the data.

So if we started at the bottom, the RollingAvg for May 29 would be (19 19 32 38)/(25 45 60 82) = 50.943 %

For the week may 22, the numbers would roll back one week, so it'd be (19 32 38 0)/(45 60 82 117) = 29.276 %

Help would be appreciated.

CodePudding user response：

transform(data.frame(lapply(df, zoo::rollsum, k=4)), roll = Appts/Leeds * 100)

  Leeds Appts      roll
1   530    38  7.169811
2   385    70 18.181818
3   304    89 29.276316
4   212   108 50.943396

CodePudding user response：

Simple solution for a calculated column in DAX:

RollingAvg = 
VAR _currentDate = [Week]
VAR _minDate = _currentDate - 4*7
RETURN
CALCULATE ( 
    DIVIDE ( 
        SUM ( 'Table'[Appts] )  ,
        SUM ( 'Table'[Leads] )
    ),
    // Lift filters on table to have all rows visible
    ALL ( 'Table' ) , 
    // Add constraints to dates for a 4-week average
    'Table'[Week] <= _currentDate , 
    'Table'[Week] > _minDate 
)

Or better yet, a measure that doesn't take up space in the data model:

RollingAvgMeasure = 
/*
Calculates the 4-week rolling average if used with the Week dimension.
Else calculates the total rolling average.
*/
VAR _currentDate = MAX ( 'Table'[Week] )
VAR _minDate = _currentDate - 4*7
VAR _movingAvg = 
CALCULATE ( 
    DIVIDE ( 
        SUM ( 'Table'[Appts] ) ,
        SUM ( 'Table'[Leads] )
    ),
    ALL ( 'Table' ) ,
    'Table'[Week] <= _currentDate ,
    'Table'[Week] > _minDate 
)
VAR _total = DIVIDE ( SUM ( 'Table'[Appts] ) , SUM ( 'Table'[Leads] ) )
RETURN
// Replace if-statement with only return of _movingAvg to display the latest 4-week value.
IF (
    ISFILTERED ( 'Table'[Week] ),
    _movingAvg , 
    _total
)