Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Aggregate data in a hourly interval

I have a direct query table with a creation date column.

 

I want to aggregate all values for one hour of certain day.

 

I can relate the values ​​read in a day through the calendar with a relationships.

 

I built this type of "hourly" calendar with these relationships.

 

 

However I cannot group the data by the hour as I do daily, i dont now why. 

 

 

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Here my date table

     

    Date = 
VAR MinYear = YEAR ( MIN ( ATEs[Date] ) )
VAR MaxYear = YEAR ( MAX ( ATEs[Date] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ); 
        AND ( YEAR ( [Date] ) >= MinYear; YEAR ( [Date] ) <= MaxYear )
    );
    "Year"; YEAR ( [Date] );
    "Month Name"; FORMAT ( [Date]; "mmmm" );
    "Month Number"; MONTH ( [Date] );
    "Weekday"; FORMAT ( [Date]; "dddd" );
    "Weekday number"; WEEKDAY( [Date] );
    "Quarter"; "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1;
    "Day of Month"; Day([Date])
)

     

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

9 Replies