Forum Discussion

lukeSDM's avatar
lukeSDM
Icon for Helper V rankHelper V
5 years ago
Solved

Weekly Rolling average for already calculated measure

Hello,   I am trying to create a weekly rolling average for my measure calculated attendance :  "([AlL Present]/[Measure])*100"   which is : "ALL Present = CALCULATE(COUNT(AttendanceOvertime[St...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    Below are the formulas for all the measures I'm using.  Without seeing what you're expected result based on the data provided, it's kind of difficult to figure out what, if anything, is wrong.  Could you please provide some details?

    ALL Present:=CALCULATE(
	COUNTROWS(AttendanceOvertime),
	AttendanceOvertime[Statistical meaning] IN {"Present", "Approved Educational Activity"}
)


ALL Present Running Total:=CALCULATE(
		[ALL Present],
		FILTER(
			ALL('Calendar'),
			'Calendar'[Date] <= MAX('Calendar'[Date])
		)
	)


Measure:=CALCULATE (
	COUNT ( AttendanceOvertime[Statistical meaning] ),
	AttendanceOvertime[Statistical meaning] IN { "Attendance not required" }
)


Measure Running Total:=CALCULATE(
		[Measure],
		FILTER(
			ALL('Calendar'),
			'Calendar'[Date] <= MAX('Calendar'[Date])
		)
	)


All Present Running Average:=DIVIDE(
		[ALL Present Running Total],
		[Measure Running Total],
		BLANK()
	)

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

lukeSDM , Not very clear.

Have Rank columnin you week and 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) // you can use YYYYWW - year Week

 

Then you can have rolling measure like

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

 

Other measures


This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))