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: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
960 Views
0
1 ACCEPTED SOLUTION
BeaBF
Solution Sage
Solution Sage
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
854 Views
16 REPLIES 16
BeaBF
Solution Sage
Solution Sage

‎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
924 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
913 Views
0
BeaBF
Solution Sage
Solution Sage
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
901 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
897 Views
0
BeaBF
Solution Sage
Solution Sage
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
888 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
886 Views
0
BeaBF
Solution Sage
Solution Sage
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
884 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
880 Views
0
BeaBF
Solution Sage
Solution Sage
In response to edejuan

‎06-16-2022 05:54 AM

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

Message 12 of 17
879 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
875 Views
0
BeaBF
Solution Sage
Solution Sage
In response to edejuan

‎06-16-2022 06:00 AM

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

Message 14 of 17
872 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
866 Views
0
BeaBF
Solution Sage
Solution Sage
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
855 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
846 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
829 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
829 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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

View All
Top Kudoed Authors
View All