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 

Could you please follow these steps written below:

1. Create a DateTime dimension at hour grain with at least:
   [DateTime] (datetime), [Date], [Hour] (0–23), [WeekdayNum] (Mon=1…Sun=7).

2. Relate DateTime[DateTime] → Deposits[DateTime] (or via a key).
3. Keep Country/Region in your fact (or a related dim); slicers/filters will flow naturally.

Measures

First, a base KPI:

Deposit Amount =
SUM ( Deposits[DepositAmount] )

Then the 7-occurrence rolling average (same weekday & hour, prior only):

Avg Deposit - 7 prior same weekday-hour =
VAR SelDateTime =
   MAX ( 'DateTime'[DateTime] )
VAR SelWeekday =
   SELECTEDVALUE ( 'DateTime'[WeekdayNum] )
VAR SelHour =
   SELECTEDVALUE ( 'DateTime'[Hour] )
VAR PriorSlots =
   TOPN (
       7,
       FILTER (
           ALL ( 'DateTime' ),                         
          'DateTime'[WeekdayNum] = SelWeekday
              && 'DateTime'[Hour] = SelHour
              && 'DateTime'[DateTime] < SelDateTime   
       ),
      'DateTime'[DateTime], DESC
   )
RETURN
IF (
   HASONEVALUE ( 'DateTime'[Hour] ) && NOT ISBLANK ( SelDateTime ),
   AVERAGEX ( PriorSlots, CALCULATE ( [Deposit Amount] ) ),
   BLANK ()
)

[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