7 previous same day rolling averages in PowerBI using DAX

[abhishekvaidhav]

Say I have data which is having at Date Hour level with some KPI called deposit amount with some category like country or region.
ON 20th March 13:00 I had a huge anamoly that resulted in a spike in the KPI, I would like the find the previous same day rolling averages using DAX in power bi or any other way. So I need rolling 7 day KPI averages for  previous same days and hour like 13th Match 13:00,6th March 13:00,27thFeb 13:00, 20TH Feb 13:00 but excludes the anamoly day.
So if there is a filter say date,hour and day and country filters suppose I select 20th Match  13:00. thursday UK, it should give me 7 same day KPI rolling averages excluding 20th March? CCan it be implemented in DAX?Please help with formulaes and steps?

[v-mdharahman]

Hi @abhishekvaidhav,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to calculate your KPI based on previous days and a fixed time for all days excluding the anamoly day(s). You also want to include country in the filters. As @rohit1991 and @danextian already responded to your query, please go through the steps provided by @rohit1991 and check if it answers your query. 

Also if you need us to give you detailed step wise solution, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

I would also take a moment to thank @rohit1991 and @danextian, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[rohit1991]

Hi @abhishekvaidhav ,
Yes, this type of analysis can definitely be implemented in Power BI using DAX. Since your data is at a Date-Hour level and includes a KPI like deposit amount, what you’re aiming to do is calculate a rolling average for the same weekday and hour over the past 7 occurrences—excluding the selected anomalous date. For example, if a user selects 20th March 13:00 (a Thursday) in the UK region, the measure should look back at previous Thursdays at 13:00 (like 13th March, 6th March, 27th Feb, etc.), filter by the same country, and compute the average of the deposit amount for those time slots, while explicitly excluding 20th March. 

To implement this, you’ll need a proper date-time dimension table with fields like date, hour, weekday name, and make sure it’s related to your main data. Using DAX, you can write a measure that first identifies the 7 most recent same-day, same-hour combinations before the selected date using FILTER and TOPN, and then calculates the average KPI using CALCULATE and AVERAGE. This approach ensures that any outlier like the 20th March spike is excluded, and you're working only with comparable, historical time slices for a more reliable trend analysis.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[danextian]

Hi @abhishekvaidhav 

It would be easier for to come up with a solution if you provides us  with a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[abhishekvaidhav]

Here is the link to the data
https://docs.google.com/spreadsheets/d/1SIADUcyvpN9Mc-fkAOts4TMO6YwIotlW/edit?usp=drive_link&ouid=10...
the data is at date hourly level
KPIS failed transactions
filters country day hour
Rolling avg on same  week  previous week days and hours.
say 20th March should have rolling avg 3 days on 13th march 6th march and 27th feb for the same hours .excluding 20th March just to understand the impact on that day with previous rolling avgs..

[hallenstal]

Ive created a desktop file with you solution, I basically created a disconnected date, table filtering R7 on the specific time and date you choose, while excluding the selected date. 

https://drive.google.com/file/d/1_EeijoZkKgOM8k2dr8afA65nTcXJzTT5/view?usp=sharing

[abhishekvaidhav]

Hey hellenstal, Thanks but I need the rolling average for same week days, say 20th march should calculate for 13th march , 6th march, feb 27th, the formula you provides is calculating for 13th march,14th march,15h march untill 19th. I need for same week days thursday 20th, should have rolling for thursday 13th, thursday 6th etc with same hours

[hallenstal]

That is a Quick fix, if you know dax, just add weekday as a condition, else I can upload a new file

[abhishekvaidhav]

Can you please me with teh file, I am quite new to DAX, Thanks again..

[hallenstal]

sure, 

Test this one instead, the condition gets last 30 days and within that period the same weekday as the one you select.

BR

https://drive.google.com/file/d/1d7lwTuD_VcfRiL1nA2iVSBrZ6UfkeD4T/view?usp=drive_link

[abhishekvaidhav]

Sure thanks a lot, file is nt accessible, have requested access. Thanks

[hallenstal]

have you received the file?

[hallenstal · Accepted solution]

fixed the access