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

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