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: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
edejuan
New Member

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
905 Views
0
1 ACCEPTED SOLUTION
BeaBF
Impactful Individual
Impactful Individual
In response to edejuan

‎06-16-2022 07:06 AM

@edejuan 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
799 Views
16 REPLIES 16
BeaBF
Impactful Individual
Impactful Individual

‎06-16-2022 03:16 AM

@edejuan Hi!

 

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

 

BF

Message 2 of 17
869 Views
0
edejuan
New Member
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
858 Views
0
BeaBF
Impactful Individual
Impactful Individual
In response to edejuan

‎06-16-2022 03:52 AM

@edejuan You can calculate a column like this:

 

= CALCULATE(AVERAGE(Tabella[Price]), ALLEXCEPT(Tabella, Tabella[Date].[Giorno]))
 
BF
Message 6 of 17
846 Views
0
edejuan
New Member
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
842 Views
0
BeaBF
Impactful Individual
Impactful Individual
In response to edejuan

‎06-16-2022 05:14 AM

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

BeaBF_0-1655381638400.png

 

Can you paste your formula?

 

BF

Message 8 of 17
833 Views
0
edejuan
New Member
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
831 Views
0
BeaBF
Impactful Individual
Impactful Individual
In response to edejuan

‎06-16-2022 05:26 AM

@edejuan I have redone the formula and it works:

BeaBF_0-1655382379333.png

 

can you paste a screen?

 

BF

Message 10 of 17
829 Views
0
edejuan
New Member
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
825 Views
0
BeaBF
Impactful Individual
Impactful Individual
In response to edejuan

‎06-16-2022 05:54 AM

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

Message 12 of 17
824 Views
0
edejuan
New Member
In response to BeaBF

‎06-16-2022 05:56 AM

Yes. 

edejuan_0-1655384154936.png

Totally lost here...

 

Message 13 of 17
820 Views
0
BeaBF
Impactful Individual
Impactful Individual
In response to edejuan

‎06-16-2022 06:00 AM

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

Message 14 of 17
817 Views
0
edejuan
New Member
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
811 Views
0
BeaBF
Impactful Individual
Impactful Individual
In response to edejuan

‎06-16-2022 07:06 AM

@edejuan 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
800 Views
edejuan
New Member
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
791 Views
edejuan
New Member
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
774 Views
0
edejuan
New Member
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
774 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All