Rolling Average that starts before the period displayed on graph

[LaurenceSD]

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

[amitchandak · Accepted solution]

@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])))

[LaurenceSD]

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