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 relati...
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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi Anonymous ,

     

    you mean something like this?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      mwegener exactly that's what I want.

       

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

       

      Thanks

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        6 years ago

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

        Please give Kudos for support.

  • dobregon's avatar
    dobregon
    Impactful Individual
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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