Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
User | Count |
---|---|
87 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |