Aggregate data in a hourly interval

[Anonymous]

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. 

[mwegener]

Hi @Anonymous,

look at this.

https://community.powerbi.com/t5/Desktop/Struggling-to-get-result-I-want-from-TopN-function/m-p/854842#M410209

I separate date and time.

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

Please give Kudos for support.

[Anonymous]

That results for a table but i need to do a chart divided by month day and time on x axis... It´s possible?

Thanks

[dobregon]

hi,

The problem is your relationship between the table called "package..." and "calendar". As you see the calendar is in hourly basis but your timeseries in your main table have timeseries like 15:49:00h so, it will not match with any hour in your calendar.

One solutions that you can do is to round your timeseries to up or down (your choice) in order to have a timeseries with date and time in round hours. then you can create the relationship one to many (from calendar to your main table)

Then you can create the visual chart using in the X Axis the timeseries in the calendar table and the values what you want in the Y axis. Remember that in the round of the timeseries could be possible that some timeseries could be repetead with different values of weight so.. you cna take the max, average or whatever agregation that you have

[Anonymous]

@dobregon i cant round my time series because i have a directquery table

[mwegener]

Hi @Anonymous ,

you mean something like this?

[Anonymous]

@mwegener exactly that's what I want.

Which variables you put on the x axis to do the chart?

Thanks

[mwegener]

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

Please give Kudos for support.

[Anonymous]

How i do to have the day of month?

[mwegener · Accepted solution]

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.