Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 , 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])))
perfect, thank you - i went with the rolling 42 days option and it worked exactly as I'd hoped, thank you