Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
abhishekvaidhav
Regular Visitor

7 previous same day rolling averages in PowerBI using DAX

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?

1 ACCEPTED SOLUTION

12 REPLIES 12
v-mdharahman
Community Support
Community Support

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
Super User
Super User

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
Super User
Super User

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.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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..

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

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

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

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

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

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

have you received the file?

fixed the access

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.