Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Repeat 24 hours average for each day

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

 

power bi
  • BeaBF's avatar
    BeaBF
    4 years ago

    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

16 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    4 years ago

    Anonymous Hi!

     

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

     

    BF

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       
      • BeaBF's avatar
        BeaBF
        Icon for Super User rankSuper User
        4 years ago

        Anonymous You can calculate a column like this:

         

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