topic Re: Repeat 24 hours average for each day in DAX Commands and Tips

Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 09:52:54 GMT

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

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

Thank you very much.
Best regards,

Enrique

Re: Repeat 24 hours average for each day

BeaBF — Thu, 16 Jun 2022 10:16:23 GMT

Anonymous Hi!

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

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 10:27:20 GMT

Sure BeaBF !

There you have.

Thanks!!

Date	Price	Daily average
19/05/2022 0:00	179,94	192,12
19/05/2022 1:00	176,27	192,12
19/05/2022 2:00	177,98	192,12
19/05/2022 3:00	185,06	192,12
19/05/2022 4:00	222,33	192,12
19/05/2022 5:00	235,7	192,12
19/05/2022 6:00	230,01	192,12
19/05/2022 7:00	215,94	192,12
19/05/2022 8:00	198,22	192,12
19/05/2022 9:00	171,59	192,12
19/05/2022 10:00	167,02	192,12
19/05/2022 11:00	163,7	192,12
19/05/2022 12:00	161,18	192,12
19/05/2022 13:00	157,14	192,12
19/05/2022 14:00	157,14	192,12
19/05/2022 15:00	162,1	192,12
19/05/2022 16:00	164,54	192,12
19/05/2022 17:00	182	192,12
19/05/2022 18:00	220,43	192,12
19/05/2022 19:00	236,21	192,12
19/05/2022 20:00	226,03	192,12
19/05/2022 21:00	213,31	192,12
19/05/2022 22:00	211,1	192,12
19/05/2022 23:00	195,98	192,12
20/05/2022 0:00	189,56	201,33
20/05/2022 1:00	188	201,33
20/05/2022 2:00	189,56	201,33
20/05/2022 3:00	208,62	201,33
20/05/2022 4:00	226,51	201,33
20/05/2022 5:00	230,05	201,33
20/05/2022 6:00	230,72	201,33
20/05/2022 7:00	224	201,33
20/05/2022 8:00	204,97	201,33
20/05/2022 9:00	200,28	201,33
20/05/2022 10:00	195	201,33
20/05/2022 11:00	190,07	201,33
20/05/2022 12:00	180,18	201,33
20/05/2022 13:00	174	201,33
20/05/2022 14:00	180,18	201,33
20/05/2022 15:00	190	201,33
20/05/2022 16:00	214,92	201,33
20/05/2022 17:00	225,63	201,33
20/05/2022 18:00	226,87	201,33
20/05/2022 19:00	218,03	201,33
20/05/2022 20:00	205,27	201,33
20/05/2022 21:00	190,17	201,33
20/05/2022 22:00	180	201,33
20/05/2022 23:00	169,27	201,33

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 10:28:07 GMT

Sure!

Sorry! There you have.

Thanks!!

Date	Price	Daily average
19/05/2022 0:00	179,94	192,12
19/05/2022 1:00	176,27	192,12
19/05/2022 2:00	177,98	192,12
19/05/2022 3:00	185,06	192,12
19/05/2022 4:00	222,33	192,12
19/05/2022 5:00	235,7	192,12
19/05/2022 6:00	230,01	192,12
19/05/2022 7:00	215,94	192,12
19/05/2022 8:00	198,22	192,12
19/05/2022 9:00	171,59	192,12
19/05/2022 10:00	167,02	192,12
19/05/2022 11:00	163,7	192,12
19/05/2022 12:00	161,18	192,12
19/05/2022 13:00	157,14	192,12
19/05/2022 14:00	157,14	192,12
19/05/2022 15:00	162,1	192,12
19/05/2022 16:00	164,54	192,12
19/05/2022 17:00	182	192,12
19/05/2022 18:00	220,43	192,12
19/05/2022 19:00	236,21	192,12
19/05/2022 20:00	226,03	192,12
19/05/2022 21:00	213,31	192,12
19/05/2022 22:00	211,1	192,12
19/05/2022 23:00	195,98	192,12
20/05/2022 0:00	189,56	201,33
20/05/2022 1:00	188	201,33
20/05/2022 2:00	189,56	201,33
20/05/2022 3:00	208,62	201,33
20/05/2022 4:00	226,51	201,33
20/05/2022 5:00	230,05	201,33
20/05/2022 6:00	230,72	201,33
20/05/2022 7:00	224	201,33
20/05/2022 8:00	204,97	201,33
20/05/2022 9:00	200,28	201,33
20/05/2022 10:00	195	201,33
20/05/2022 11:00	190,07	201,33
20/05/2022 12:00	180,18	201,33
20/05/2022 13:00	174	201,33
20/05/2022 14:00	180,18	201,33
20/05/2022 15:00	190	201,33
20/05/2022 16:00	214,92	201,33
20/05/2022 17:00	225,63	201,33
20/05/2022 18:00	226,87	201,33
20/05/2022 19:00	218,03	201,33
20/05/2022 20:00	205,27	201,33
20/05/2022 21:00	190,17	201,33
20/05/2022 22:00	180	201,33
20/05/2022 23:00	169,27	201,33

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 10:34:45 GMT

Hi BeaBF

Sure! Here you have. Thanks!

Date	Price	Daily average
19/05/2022 0:00	179,94	192,12
19/05/2022 1:00	176,27	192,12
19/05/2022 2:00	177,98	192,12
19/05/2022 3:00	185,06	192,12
19/05/2022 4:00	222,33	192,12
19/05/2022 5:00	235,7	192,12
19/05/2022 6:00	230,01	192,12
19/05/2022 7:00	215,94	192,12
19/05/2022 8:00	198,22	192,12
19/05/2022 9:00	171,59	192,12
19/05/2022 10:00	167,02	192,12
19/05/2022 11:00	163,7	192,12
19/05/2022 12:00	161,18	192,12
19/05/2022 13:00	157,14	192,12
19/05/2022 14:00	157,14	192,12
19/05/2022 15:00	162,1	192,12
19/05/2022 16:00	164,54	192,12
19/05/2022 17:00	182	192,12
19/05/2022 18:00	220,43	192,12
19/05/2022 19:00	236,21	192,12
19/05/2022 20:00	226,03	192,12
19/05/2022 21:00	213,31	192,12
19/05/2022 22:00	211,1	192,12
19/05/2022 23:00	195,98	192,12
20/05/2022 0:00	189,56	201,33
20/05/2022 1:00	188	201,33
20/05/2022 2:00	189,56	201,33
20/05/2022 3:00	208,62	201,33
20/05/2022 4:00	226,51	201,33
20/05/2022 5:00	230,05	201,33
20/05/2022 6:00	230,72	201,33
20/05/2022 7:00	224	201,33
20/05/2022 8:00	204,97	201,33
20/05/2022 9:00	200,28	201,33
20/05/2022 10:00	195	201,33
20/05/2022 11:00	190,07	201,33
20/05/2022 12:00	180,18	201,33
20/05/2022 13:00	174	201,33
20/05/2022 14:00	180,18	201,33
20/05/2022 15:00	190	201,33
20/05/2022 16:00	214,92	201,33
20/05/2022 17:00	225,63	201,33
20/05/2022 18:00	226,87	201,33
20/05/2022 19:00	218,03	201,33
20/05/2022 20:00	205,27	201,33
20/05/2022 21:00	190,17	201,33
20/05/2022 22:00	180	201,33
20/05/2022 23:00	169,27	201,33
21/05/2022 0:00	169,26	180,57
21/05/2022 1:00	165,87	180,57
21/05/2022 2:00	164,84	180,57
21/05/2022 3:00	164,94	180,57
21/05/2022 4:00	164,84	180,57
21/05/2022 5:00	164,85	180,57
21/05/2022 6:00	166,54	180,57
21/05/2022 7:00	172,97	180,57
21/05/2022 8:00	178,82	180,57
21/05/2022 9:00	174,89	180,57
21/05/2022 10:00	170,57	180,57
21/05/2022 11:00	167,45	180,57
21/05/2022 12:00	164,67	180,57
21/05/2022 13:00	161,55	180,57
21/05/2022 14:00	159,33	180,57
21/05/2022 15:00	164,84	180,57
21/05/2022 16:00	171,58	180,57
21/05/2022 17:00	201	180,57
21/05/2022 18:00	219,32	180,57
21/05/2022 19:00	228,11	180,57
21/05/2022 20:00	227,05	180,57
21/05/2022 21:00	216,42	180,57
21/05/2022 22:00	214,57	180,57
21/05/2022 23:00	179,4	180,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

Re: Repeat 24 hours average for each day

BeaBF — Thu, 16 Jun 2022 10:52:37 GMT

Anonymous You can calculate a column like this:

= CALCULATE(AVERAGE(Tabella[Price]), ALLEXCEPT(Tabella, Tabella[Date].[Giorno]))

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 11:44:56 GMT

Hi BeaBF
Thank you very much for your formula.

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

Date	Price	Daily average (expected result)	BeaBF
19/05/2022 0:00	179,94	192,12	65,94
19/05/2022 1:00	176,27	192,12	65,94
19/05/2022 2:00	177,98	192,12	65,94
19/05/2022 3:00	185,06	192,12	65,94
19/05/2022 4:00	222,33	192,12	65,94
19/05/2022 5:00	235,7	192,12	65,94
19/05/2022 6:00	230,01	192,12	65,94
19/05/2022 7:00	215,94	192,12	65,94
19/05/2022 8:00	198,22	192,12	65,94
19/05/2022 9:00	171,59	192,12	65,94
19/05/2022 10:00	167,02	192,12	65,94
19/05/2022 11:00	163,7	192,12	65,94
19/05/2022 12:00	161,18	192,12	65,94
19/05/2022 13:00	157,14	192,12	65,94
19/05/2022 14:00	157,14	192,12	65,94
19/05/2022 15:00	162,1	192,12	65,94
19/05/2022 16:00	164,54	192,12	65,94
19/05/2022 17:00	182	192,12	65,94
19/05/2022 18:00	220,43	192,12	65,94
19/05/2022 19:00	236,21	192,12	65,94
19/05/2022 20:00	226,03	192,12	65,94
19/05/2022 21:00	213,31	192,12	65,94
19/05/2022 22:00	211,1	192,12	65,94
19/05/2022 23:00	195,98	192,12	65,94
20/05/2022 0:00	189,56	201,33	63,94
20/05/2022 1:00	188	201,33	63,94
20/05/2022 2:00	189,56	201,33	63,94
20/05/2022 3:00	208,62	201,33	63,94
20/05/2022 4:00	226,51	201,33	63,94
20/05/2022 5:00	230,05	201,33	63,94
20/05/2022 6:00	230,72	201,33	63,94
20/05/2022 7:00	224	201,33	63,94
20/05/2022 8:00	204,97	201,33	63,94
20/05/2022 9:00	200,28	201,33	63,94
20/05/2022 10:00	195	201,33	63,94
20/05/2022 11:00	190,07	201,33	63,94
20/05/2022 12:00	180,18	201,33	63,94
20/05/2022 13:00	174	201,33	63,94
20/05/2022 14:00	180,18	201,33	63,94
20/05/2022 15:00	190	201,33	63,94
20/05/2022 16:00	214,92	201,33	63,94
20/05/2022 17:00	225,63	201,33	63,94
20/05/2022 18:00	226,87	201,33	63,94
20/05/2022 19:00	218,03	201,33	63,94
20/05/2022 20:00	205,27	201,33	63,94
20/05/2022 21:00	190,17	201,33	63,94
20/05/2022 22:00	180	201,33	63,94
20/05/2022 23:00	169,27	201,33	63,94

Re: Repeat 24 hours average for each day

BeaBF — Thu, 16 Jun 2022 12:14:38 GMT

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

Can you paste your formula?

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 12:22:07 GMT

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'

Re: Repeat 24 hours average for each day

BeaBF — Thu, 16 Jun 2022 12:26:33 GMT

Anonymous I have redone the formula and it works:

can you paste a screen?

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 12:50:42 GMT

Hi BeaBF ,

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

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

Re: Repeat 24 hours average for each day

BeaBF — Thu, 16 Jun 2022 12:54:15 GMT

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

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 12:56:45 GMT

Yes.

Totally lost here...

Re: Repeat 24 hours average for each day

BeaBF — Thu, 16 Jun 2022 13:00:05 GMT

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

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 13:37:31 GMT

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

Thanks for your help!!!

Re: Repeat 24 hours average for each day

BeaBF — Thu, 16 Jun 2022 14:06:02 GMT

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!

Re: Repeat 24 hours average for each day

Anonymous — Thu, 16 Jun 2022 14:11:47 GMT

IT WORKS!!!

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