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 B...
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
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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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
        
    • BeaBF's avatar
      BeaBF
      Icon for Super User rankSuper User
      4 years ago

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

       

      Can you paste your formula?

       

      BF

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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'