Forum Discussion
Solved
Power BI DAX for computing same 7 same week day and hour average(rolling)
Hi, I am quite new to POWER BI DAX, so I am working on a task to find anomolies in the trend data. The data provided to me is at daily - hourly level across different categories with a KPI value fr...
Just replace the formula by this one:
Average Value = VAR maxdate = MAX('calendar'[Date]) VAR dayofwee = WEEKDAY(maxdate) RETURN DIVIDE ( CALCULATE( SUM('Final - Dataset'[KPI]), ALL('calendar'), 'calendar'[Date] < maxdate && 'calendar'[Date] > (maxdate - (7 * 7)) && 'calendar'[weekday] = dayofwee ) ,7)
Thanks a lot in the code provided , it includes the anamoly date 31st march 21:00 PM, however we dont want to consider the anomoly date in teh calculation we want days other than 31st i.e
( 24th March 21:00 + 17th March 21:00 + 10th March 21:00 + 3rd March 21:00 + 24th Feb 21:00 + 17th Feb 21:00)/ 7.Can you help to tweak that part thanks again..
MFelix
Super User
Super UserJust replace the formula by this one:
Average Value =
VAR maxdate = MAX('calendar'[Date])
VAR dayofwee = WEEKDAY(maxdate)
RETURN
DIVIDE ( CALCULATE(
SUM('Final - Dataset'[KPI]),
ALL('calendar'),
'calendar'[Date] < maxdate && 'calendar'[Date] > (maxdate - (7 * 7)) && 'calendar'[weekday] = dayofwee
) ,7)