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

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