cancel
Showing results for
Did you mean:
Helper I

## Rolling Average that starts before the period displayed on graph

Hi there,

So I'm trying to get a rolling average of conversion into my report, and the period I'm looking at doing this is the past 6 weeks and the measure is conversion.

So I've start off with calculating the cumulative Bookings and Enquiries with these formulas

Cumulative Bkgs = sumx(FILTER(ALLSELECTED(Dates),Dates[Dates]<=MAX(Dates[Dates])),[Bkgs])
Cumulative Enqs = sumx(FILTER(ALLSELECTED(Dates),Dates[Dates]<=MAX(Dates[Dates])),[Enqs])

& then I'm dividing the bookings by the enquiries to get my conversion. I've then plotted this on a graph, based on weeks and filtered the relative date for the last 6 weeks, however I've realised that doing it this way for the 1st week only gives me that weeks average and for the 2ns week, I get an average of those 2 weeks, it's not until the 6the week on the graph that this is a true 6 week average.

& now I'm a bit stuck.....

What I'd really like is for the 1st week shown on the graph to be an average of that week and the 5 weeks before it and so on, does anyone know a way around this? I can't seem to come up with a solution that brings in the prior data, apart from running the sheet for 12 weeks and telling people to ignore the first 6 weeks, which isn't going to cut it......

Thanks

Laurence
1 ACCEPTED SOLUTION
Super User IV

@LaurenceSD , With Date table 2 option , rolling 42 days  or use week rank

Rolling 6 week  = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-42,DAY))

Week wise

New column in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

or
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYMM format

measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Last 6 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Proud to be a Super User!

2 REPLIES 2
Super User IV

@LaurenceSD , With Date table 2 option , rolling 42 days  or use week rank

Rolling 6 week  = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-42,DAY))

Week wise

New column in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

or
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYMM format

measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Last 6 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Proud to be a Super User!

Helper I

perfect, thank you - i went with the rolling 42 days option and it worked exactly as I'd hoped, thank you

Announcements