Forum Discussion

baronraghu's avatar
baronraghu
Icon for Helper III rankHelper 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-2018 22:45:00 23:00:00 1999.69 01-01-2018 23:00:00 23:...
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

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
    Icon for Helper III rankHelper 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
      Icon for Helper III rankHelper 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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