Forum Discussion

baronraghu's avatar
baronraghu
Helper III
7 years ago
Solved

rolling average for hourly data

Hi All,

 

I have following data Column 1 denotes Date, Column 2 start time Column 3 End Time Column 4 Price

 

    
01-01-201822:45:0023:00:001999.69
01-01-201823:00:0023:15:001999.79
01-01-201823:15:0023:30:001899.72
01-01-201823:30:0023:45:001894.18
01-01-201823:45:0000:00:001749.99
02-01-201800:00:0000:15:001999.69
02-01-201800:15:0000:30:001999.07
02-01-201800:30:0000:45:001801.79
02-01-201800:45:0001:00:001791.81
02-01-201801:00:0001:15:001954.14
02-01-201801:15:0001:30:001801.46
02-01-201801:30:0001:45:001800.81
02-01-201801:45:0002:00:001749.47
02-01-201802:00:0002:15:001793.62
02-01-201802:15:0002:30:001780.16
02-01-201802:30:0002:45:001698.83
02-01-201802:45:0003:00:001698.92
02-01-201803:00:0003:15:001780.75
02-01-201803:15:0003:30:001787.65
02-01-201803:30:0003:45:001999.06
02-01-201803:45:0004:00:001999.15
02-01-201804:00:0004:15:001801.68
02-01-201804:15:0004:30:001999.03
02-01-201804:30:0004:45:001801.45
02-01-201804:45:0005:00:001999.39
02-01-201805:00:0005:15:002180.26
02-01-201805:15:0005:30:002318.63
02-01-201805:30:0005:45:002437.46
02-01-201805:45:0006:00:002402.88
02-01-201806:00:0006:15:002679.67


I am looking to calculate the following

1) Average rolling price per 4 hour spread per month as shown below

 

start timeend timeJanuary
00:00:0004:00:001938.8
01:00:0005:00:002005
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi baronraghu,

     

    You can refer to following calculate table formula to get a summarize table base on sample data table:

    Summary =
ADDCOLUMNS (
    SUMMARIZE (
        FILTER ( 'Sample', MINUTE ( [StartTime] ) = 0 && SECOND ( [StartTime] ) = 0 ),
        [Date],
        [StartTime],
        "End", CALCULATE ( MAX ( 'Sample'[EndTime] ), ALLEXCEPT ( 'Sample', 'Sample'[Date] ) )
    ),
    "AVG", AVERAGEX (
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Date] = EARLIER ( [Date] )
                && 'Sample'[StartTime] >= EARLIER ( [StartTime] )
                && 'Sample'[EndTime] <= EARLIER ( [End] )
        ),
        [Value]
    )
)

     

    Regards,
    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi baronraghu,

     

    You can refer to following steps to create a table visual to display range average data.

     

    Steps:

    1. Create a calculate table with hour and range.

    HourList = SELECTCOLUMNS(GENERATESERIES(0,23,1),"Hour",[Value],"Range",INT([Value]/4)+1)

    2. Add calculated column to original table to extract hour from start time.

    StartHour = HOUR([Start])

    3. Create table visual.

     

    Regards,

    Xiaoxin Sheng

    • baronraghu's avatar
      baronraghu
      Helper III
      7 years ago

      Hi, 

       

      I think the below table will be helpful

       

      01-01-201800:00:0000:15:001999.21
      01-01-201800:15:0000:30:001899.96
      01-01-201800:30:0000:45:001899.51
      01-01-201800:45:0001:00:001899.13
      01-01-201801:00:0001:15:001819.77
      01-01-201801:15:0001:30:001819.32
      01-01-201801:30:0001:45:001800.78
      01-01-201801:45:0002:00:001749.69
      01-01-201802:00:0002:15:001749.57
      01-01-201802:15:0002:30:001749.45
      01-01-201802:30:0002:45:001749.58
      01-01-201802:45:0003:00:001749.83
      01-01-201803:00:0003:15:001749.65
      01-01-201803:15:0003:30:001749.71
      01-01-201803:30:0003:45:001749.71
      01-01-201803:45:0004:00:001999.01
      02-01-201800:00:0000:15:001999.69
      02-01-201800:15:0000:30:001999.07
      02-01-201800:30:0000:45:001801.79
      02-01-201800:45:0001:00:001791.81
      02-01-201801:00:0001:15:001954.14
      02-01-201801:15:0001:30:001801.46
      02-01-201801:30:0001:45:001800.81
      02-01-201801:45:0002:00:001749.47
      02-01-201802:00:0002:15:001793.62
      02-01-201802:15:0002:30:001780.16
      02-01-201802:30:0002:45:001698.83
      02-01-201802:45:0003:00:001698.92
      02-01-201803:00:0003:15:001780.75
      02-01-201803:15:0003:30:001787.65
      02-01-201803:30:0003:45:001999.06
      02-01-201803:45:0004:00:001999.15
      03-01-201800:00:0000:15:001999.77
      03-01-201800:15:0000:30:001999.33
      03-01-201800:30:0000:45:001899.85
      03-01-201800:45:0001:00:001899.21
      03-01-201801:00:0001:15:001801.93
      03-01-201801:15:0001:30:001801.9
      03-01-201801:30:0001:45:001801.73
      03-01-201801:45:0002:00:001801.48
      03-01-201802:00:0002:15:001780.53
      03-01-201802:15:0002:30:001700.99
      03-01-201802:30:0002:45:001700.78
      03-01-201802:45:0003:00:001700.33
      03-01-201803:00:0003:15:001698.99
      03-01-201803:15:0003:30:001801.19
      03-01-201803:30:0003:45:001780.44
      03-01-201803:45:0004:00:001800.79

       

       

      What I would like is a below table sumarizing the average price as

       

      DateStart TimeEnd TimeAver Price
      01-01-201800:00:0004:00:001938
      01-01-201801:00:0004:00:001863
      01-01-201802:00:0004:00:00786
      02-01-201800:00:0004:00:002589
      02-01-201801:00:0004:00:001587
      02-01-201802:00:0004:00:001532
      03-01-201800:00:0004:00:001486
      03-01-201801:00:0004:00:001436
      03-01-201802:00:0004:00:001428

       

       

      • baronraghu's avatar
        baronraghu
        Helper III
        7 years ago

        In lieu with my previous post I have created a new bucket table like below

         

        Adj_Start     Ad_End

        00:00:0004:00:00
        01:00:0004:00:00
        02:00:0004:00:00

         

        I created a dax formula like this

         

        Measure = CALCULATE([average price per day],FILTER(Append1,Append1[Start Time]>=VALUES(bucket[Adj_Start])&&Append1[Start Time]<VALUES(bucket[Ad_End])))

         

        This doesnt seem to work