cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling 4 week average conversion rate

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 itd be (19+32+38+0)/(45+60+82+117) = 29.276 %

Help would be appricated.

1 ACCEPTED SOLUTION
Super User

@Jacob1832 , Try a new column like

new column =
var _date = [Week]
return
divide(sumx(filter(Table, [Week] > _date -28 && [Week]<= _date ),[Appts]),sumx(filter(Table, [Week] > _date -28 && [Week]<= _date ),[leads]))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
2 REPLIES 2
Super User

@Jacob1832 , Try a new column like

new column =
var _date = [Week]
return
divide(sumx(filter(Table, [Week] > _date -28 && [Week]<= _date ),[Appts]),sumx(filter(Table, [Week] > _date -28 && [Week]<= _date ),[leads]))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Frequent Visitor

How would i adjust the calculation if i had a single number (Actuals) and a grouping variable (state)?
The rolling average would still have the same numerator, the denominator would now be 4 since its a 4 week rolling average and not a conversion rate. How would I incorporate the group of state?

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors