Forum Discussion
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 from Jan 1st 2025 to March 31st 2025.
So, Suppose I have an anomoly in the KPI on 31st March 2025 21:00PM and want to define the correct way based on 7 previous same day averages.
so lets say the for the input 31st March 2025 21:00, the 7 day rolling average for the kpi should be calcuated as ( 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
and result should be displayed for the date 31st march 21:00.
How to write it in dax, with filters on date, hours and other categories.
sharing the data for the same:
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)
7 Replies
- MFelix
Super User
Hi rekradha ,
I assume you have a calendar table and also a weekday on your table try the following code:
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 MFelix, let me try it. Looks like a great solution..
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
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)
v-kathullacCommunity Support
Hi rekradha ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
- v-kathullac
Hi @rekradha ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
- v-kathullac
Hi @rekradha ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
