Forum Discussion
lukeSDM
Helper V
5 years agoWeekly 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...
- 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() )
littlemojopuppy
Community Champion
5 years agoQuestion...are you looking for week to week or a rolling seven day period?
Rolling seven days would be calculated like this. Modify as appropriate for your data model...
CALCULATE(
[ALL Present],
DATESINPERIOD(
'Calendar'[Date],
LASTDATE('Calendar'[Date]),
-7,
DAY
)
)
- lukeSDM5 years ago
Helper V
I am looking to get a cumulative figure, so after 2 weeks I would get the average, of the first and second week, then after 3 weeks it would average of the 1st 2nd and 3rd week.
Does that make sense at will your previous dax work?
Many thanks