cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaurenceSD
Helper I
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
amitchandak
Super User IV
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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.