Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Repeat 24 hours average for each day

‎06-16-2022 02:52 AM

Hi all,

First post here.

I’ve gathered the forum and part of Google. I've found similar topics, but I cannot make it work, I’d be grateful for your help.

 

I have an hourly time series in Power BI ('Date' and 'Price').
I need to fill a column ('Daily average') REPEATING each ROW the average of the 24h of that day, the key challenge is to change the average when the day changes.

I’ve been trying different approaches with AVERAGEX, VALUES… but I cannot get to find the solution.

It’s easier to see in this example from Excel: 

The 'Daily average' is the average of the 24 numbers in column 'Price'. 

edejuan_0-1655372245157.png

I'm completely stuck. How could I do it in Power BI (DAX)? 

Thank you very much. 
Best regards, 

Enrique

 

Labels:
Message 1 of 17
1,566 Views
0
1 ACCEPTED SOLUTION
BeaBF
Super User
Super User
In response to Anonymous

‎06-16-2022 07:06 AM

@Anonymous Soooorry, a huge mistake!!

The correct formula:

 

= CALCULATE(AVERAGE(Prod_ES[Price]), ALLEXCEPT(Prod_ES,Prod_ES[date].[Date]))
 
the first one took into account only the day, but you have a lot of years and  months!
 
BF

View solution in original post

Message 16 of 17
1,460 Views
16 REPLIES 16
BeaBF
Super User
Super User

‎06-16-2022 03:16 AM

@Anonymous Hi!

 

can you paste the data as a table and not an image?

 

BF

Message 2 of 17
1,530 Views
0
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 03:34 AM

Hi @BeaBF 

Sure! Here you have. Thanks! 

 

DatePriceDaily average
19/05/2022 0:00179,94192,12
19/05/2022 1:00176,27192,12
19/05/2022 2:00177,98192,12
19/05/2022 3:00185,06192,12
19/05/2022 4:00222,33192,12
19/05/2022 5:00235,7192,12
19/05/2022 6:00230,01192,12
19/05/2022 7:00215,94192,12
19/05/2022 8:00198,22192,12
19/05/2022 9:00171,59192,12
19/05/2022 10:00167,02192,12
19/05/2022 11:00163,7192,12
19/05/2022 12:00161,18192,12
19/05/2022 13:00157,14192,12
19/05/2022 14:00157,14192,12
19/05/2022 15:00162,1192,12
19/05/2022 16:00164,54192,12
19/05/2022 17:00182192,12
19/05/2022 18:00220,43192,12
19/05/2022 19:00236,21192,12
19/05/2022 20:00226,03192,12
19/05/2022 21:00213,31192,12
19/05/2022 22:00211,1192,12
19/05/2022 23:00195,98192,12
20/05/2022 0:00189,56201,33
20/05/2022 1:00188201,33
20/05/2022 2:00189,56201,33
20/05/2022 3:00208,62201,33
20/05/2022 4:00226,51201,33
20/05/2022 5:00230,05201,33
20/05/2022 6:00230,72201,33
20/05/2022 7:00224201,33
20/05/2022 8:00204,97201,33
20/05/2022 9:00200,28201,33
20/05/2022 10:00195201,33
20/05/2022 11:00190,07201,33
20/05/2022 12:00180,18201,33
20/05/2022 13:00174201,33
20/05/2022 14:00180,18201,33
20/05/2022 15:00190201,33
20/05/2022 16:00214,92201,33
20/05/2022 17:00225,63201,33
20/05/2022 18:00226,87201,33
20/05/2022 19:00218,03201,33
20/05/2022 20:00205,27201,33
20/05/2022 21:00190,17201,33
20/05/2022 22:00180201,33
20/05/2022 23:00169,27201,33
21/05/2022 0:00169,26180,57
21/05/2022 1:00165,87180,57
21/05/2022 2:00164,84180,57
21/05/2022 3:00164,94180,57
21/05/2022 4:00164,84180,57
21/05/2022 5:00164,85180,57
21/05/2022 6:00166,54180,57
21/05/2022 7:00172,97180,57
21/05/2022 8:00178,82180,57
21/05/2022 9:00174,89180,57
21/05/2022 10:00170,57180,57
21/05/2022 11:00167,45180,57
21/05/2022 12:00164,67180,57
21/05/2022 13:00161,55180,57
21/05/2022 14:00159,33180,57
21/05/2022 15:00164,84180,57
21/05/2022 16:00171,58180,57
21/05/2022 17:00201180,57
21/05/2022 18:00219,32180,57
21/05/2022 19:00228,11180,57
21/05/2022 20:00227,05180,57
21/05/2022 21:00216,42180,57
21/05/2022 22:00214,57180,57
21/05/2022 23:00179,4180,57

 

 

(I'm having problems with posting, maybe my wifi, I've replied a couple of times but don't see my reply)

 

Thanks again

 
Message 5 of 17
1,519 Views
0
BeaBF
Super User
Super User
In response to Anonymous

‎06-16-2022 03:52 AM

@Anonymous You can calculate a column like this:

 

= CALCULATE(AVERAGE(Tabella[Price]), ALLEXCEPT(Tabella, Tabella[Date].[Giorno]))
 
BF
Message 6 of 17
1,507 Views
0
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 04:44 AM

Hi @BeaBF 
Thank you very much for your formula. 

I'm afraid it doesn't work, it returns this: 

 

DatePriceDaily average
(expected result)		BeaBF
19/05/2022 0:00179,94192,1265,94
19/05/2022 1:00176,27192,1265,94
19/05/2022 2:00177,98192,1265,94
19/05/2022 3:00185,06192,1265,94
19/05/2022 4:00222,33192,1265,94
19/05/2022 5:00235,7192,1265,94
19/05/2022 6:00230,01192,1265,94
19/05/2022 7:00215,94192,1265,94
19/05/2022 8:00198,22192,1265,94
19/05/2022 9:00171,59192,1265,94
19/05/2022 10:00167,02192,1265,94
19/05/2022 11:00163,7192,1265,94
19/05/2022 12:00161,18192,1265,94
19/05/2022 13:00157,14192,1265,94
19/05/2022 14:00157,14192,1265,94
19/05/2022 15:00162,1192,1265,94
19/05/2022 16:00164,54192,1265,94
19/05/2022 17:00182192,1265,94
19/05/2022 18:00220,43192,1265,94
19/05/2022 19:00236,21192,1265,94
19/05/2022 20:00226,03192,1265,94
19/05/2022 21:00213,31192,1265,94
19/05/2022 22:00211,1192,1265,94
19/05/2022 23:00195,98192,1265,94
20/05/2022 0:00189,56201,3363,94
20/05/2022 1:00188201,3363,94
20/05/2022 2:00189,56201,3363,94
20/05/2022 3:00208,62201,3363,94
20/05/2022 4:00226,51201,3363,94
20/05/2022 5:00230,05201,3363,94
20/05/2022 6:00230,72201,3363,94
20/05/2022 7:00224201,3363,94
20/05/2022 8:00204,97201,3363,94
20/05/2022 9:00200,28201,3363,94
20/05/2022 10:00195201,3363,94
20/05/2022 11:00190,07201,3363,94
20/05/2022 12:00180,18201,3363,94
20/05/2022 13:00174201,3363,94
20/05/2022 14:00180,18201,3363,94
20/05/2022 15:00190201,3363,94
20/05/2022 16:00214,92201,3363,94
20/05/2022 17:00225,63201,3363,94
20/05/2022 18:00226,87201,3363,94
20/05/2022 19:00218,03201,3363,94
20/05/2022 20:00205,27201,3363,94
20/05/2022 21:00190,17201,3363,94
20/05/2022 22:00180201,3363,94
20/05/2022 23:00169,27201,3363,94
    
Message 7 of 17
1,503 Views
0
BeaBF
Super User
Super User
In response to Anonymous

‎06-16-2022 05:14 AM

@Anonymous no, i've just tried it and it works:

BeaBF_0-1655381638400.png

 

Can you paste your formula?

 

BF

Message 8 of 17
1,494 Views
0
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 05:22 AM

Hi @BeaBF , thanks again for your patiente. 
This is my formula

= CALCULATE(AVERAGE('Tabla'[Price]), ALLEXCEPT('Tabla',Tabla[date].[Day]))
'Day' is in a Hierarchy in 'date'
 
Message 9 of 17
1,492 Views
0
BeaBF
Super User
Super User
In response to Anonymous

‎06-16-2022 05:26 AM

@Anonymous I have redone the formula and it works:

BeaBF_0-1655382379333.png

 

can you paste a screen?

 

BF

Message 10 of 17
1,490 Views
0
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 05:50 AM

Hi @BeaBF , 

Here's a screenshot. I've overlayed in red the expected result. 
I'm scratching my head. I don't understand. 

edejuan_0-1655383666419.png

 

When done in a small dataset, your formula works OK 🙂
Buen when I apply it to the 65k rows model, it doesn't. 
Mystery! 

 

 

Message 11 of 17
1,486 Views
0
BeaBF
Super User
Super User
In response to Anonymous

‎06-16-2022 05:54 AM

@Anonymous The field date is in datetime type, right? uhmmm 

Message 12 of 17
1,485 Views
0
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 05:56 AM

Yes. 

edejuan_0-1655384154936.png

Totally lost here...

 

Message 13 of 17
1,481 Views
0
BeaBF
Super User
Super User
In response to Anonymous

‎06-16-2022 06:00 AM

@Anonymous ok, plan B. Can you send me your pbix? 

Message 14 of 17
1,478 Views
0
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 06:37 AM

Hi @BeaBF, I've sent you a Wetransfer link via private message. 

Thanks for your help!!!

Message 15 of 17
1,472 Views
0
BeaBF
Super User
Super User
In response to Anonymous

‎06-16-2022 07:06 AM

@Anonymous Soooorry, a huge mistake!!

The correct formula:

 

= CALCULATE(AVERAGE(Prod_ES[Price]), ALLEXCEPT(Prod_ES,Prod_ES[date].[Date]))
 
the first one took into account only the day, but you have a lot of years and  months!
 
BF
Message 16 of 17
1,461 Views
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 07:11 AM

IT WORKS!!! 

Thank you thank you thank you @BeaBF , you're simply the best!!! 🙂 

Message 17 of 17
1,452 Views
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 03:28 AM

Sure! 

Sorry! There you have. 

Thanks!! 

 

 

DatePriceDaily average
19/05/2022 0:00179,94192,12
19/05/2022 1:00176,27192,12
19/05/2022 2:00177,98192,12
19/05/2022 3:00185,06192,12
19/05/2022 4:00222,33192,12
19/05/2022 5:00235,7192,12
19/05/2022 6:00230,01192,12
19/05/2022 7:00215,94192,12
19/05/2022 8:00198,22192,12
19/05/2022 9:00171,59192,12
19/05/2022 10:00167,02192,12
19/05/2022 11:00163,7192,12
19/05/2022 12:00161,18192,12
19/05/2022 13:00157,14192,12
19/05/2022 14:00157,14192,12
19/05/2022 15:00162,1192,12
19/05/2022 16:00164,54192,12
19/05/2022 17:00182192,12
19/05/2022 18:00220,43192,12
19/05/2022 19:00236,21192,12
19/05/2022 20:00226,03192,12
19/05/2022 21:00213,31192,12
19/05/2022 22:00211,1192,12
19/05/2022 23:00195,98192,12
20/05/2022 0:00189,56201,33
20/05/2022 1:00188201,33
20/05/2022 2:00189,56201,33
20/05/2022 3:00208,62201,33
20/05/2022 4:00226,51201,33
20/05/2022 5:00230,05201,33
20/05/2022 6:00230,72201,33
20/05/2022 7:00224201,33
20/05/2022 8:00204,97201,33
20/05/2022 9:00200,28201,33
20/05/2022 10:00195201,33
20/05/2022 11:00190,07201,33
20/05/2022 12:00180,18201,33
20/05/2022 13:00174201,33
20/05/2022 14:00180,18201,33
20/05/2022 15:00190201,33
20/05/2022 16:00214,92201,33
20/05/2022 17:00225,63201,33
20/05/2022 18:00226,87201,33
20/05/2022 19:00218,03201,33
20/05/2022 20:00205,27201,33
20/05/2022 21:00190,17201,33
20/05/2022 22:00180201,33
20/05/2022 23:00169,27201,33

 

Message 4 of 17
1,435 Views
0
Anonymous
Not applicable
In response to BeaBF

‎06-16-2022 03:27 AM

Sure @BeaBF ! 

There you have. 

Thanks!! 

 

 

DatePriceDaily average
19/05/2022 0:00179,94192,12
19/05/2022 1:00176,27192,12
19/05/2022 2:00177,98192,12
19/05/2022 3:00185,06192,12
19/05/2022 4:00222,33192,12
19/05/2022 5:00235,7192,12
19/05/2022 6:00230,01192,12
19/05/2022 7:00215,94192,12
19/05/2022 8:00198,22192,12
19/05/2022 9:00171,59192,12
19/05/2022 10:00167,02192,12
19/05/2022 11:00163,7192,12
19/05/2022 12:00161,18192,12
19/05/2022 13:00157,14192,12
19/05/2022 14:00157,14192,12
19/05/2022 15:00162,1192,12
19/05/2022 16:00164,54192,12
19/05/2022 17:00182192,12
19/05/2022 18:00220,43192,12
19/05/2022 19:00236,21192,12
19/05/2022 20:00226,03192,12
19/05/2022 21:00213,31192,12
19/05/2022 22:00211,1192,12
19/05/2022 23:00195,98192,12
20/05/2022 0:00189,56201,33
20/05/2022 1:00188201,33
20/05/2022 2:00189,56201,33
20/05/2022 3:00208,62201,33
20/05/2022 4:00226,51201,33
20/05/2022 5:00230,05201,33
20/05/2022 6:00230,72201,33
20/05/2022 7:00224201,33
20/05/2022 8:00204,97201,33
20/05/2022 9:00200,28201,33
20/05/2022 10:00195201,33
20/05/2022 11:00190,07201,33
20/05/2022 12:00180,18201,33
20/05/2022 13:00174201,33
20/05/2022 14:00180,18201,33
20/05/2022 15:00190201,33
20/05/2022 16:00214,92201,33
20/05/2022 17:00225,63201,33
20/05/2022 18:00226,87201,33
20/05/2022 19:00218,03201,33
20/05/2022 20:00205,27201,33
20/05/2022 21:00190,17201,33
20/05/2022 22:00180201,33
20/05/2022 23:00169,27201,33

 

Message 3 of 17
1,435 Views
0

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All