Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a trend graph with date and time

Hi

 

I have a table A0 that has the following structure and sample values:

DateTimeUsername
3/11/20227:19:16 AMabc
3/13/202211:20:56 PMxyx
3/13/202212:25:44 PMfff

 

I want to create a trend graph as follows:

 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi Anonymous ,

     

    You can try the following methods.

    Column:

    Time Interval = 
IF ( HOUR ( [Time] ) >= 12, HOUR ( [Time] ) - 12, HOUR ( [Time] ) ) & "-"
    & IF ( HOUR ( [Time] ) >= 12, HOUR ( [Time] ) - 12 + 1, HOUR ( [Time] ) + 1 )
    & IF ( HOUR ( [Time] ) >= 12, "PM", "AM" )
    Username (#) =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( 'Table', 'Table'[Time Interval] = EARLIER ( 'Table'[Time Interval] ) )
)

    Index is only used for sorting.

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Your sample data doesn't match the output. Please provide sanitized sample data that fully covers your issue.

    Please show the expected outcome based on the sample data you provided.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi lbendlin 

      The data would look like following:

      DateTimeUsername

      3/11/221:01:00 AMa1
      3/11/222:10:00 AMa2
      3/11/222:34:00 AMa3
      3/11/223:10:00 AMa4
      3/11/223:10:00 AMa5
      3/11/223:10:00 AMa6
      3/11/224:10:00 AMa7
      3/11/224:10:00 AMa8
      3/11/224:11:00 AMa8
      3/11/224:12:00 AMa8
      3/11/225:10:00 AMa11
      3/11/225:10:00 AMa12
      3/11/225:10:00 AMa13
      3/11/225:10:00 AMa14
      3/11/225:10:00 AMa15
      3/11/226:10:00 AMa16
      3/11/226:10:00 AMa17
      3/11/226:10:00 AMa18
      3/11/226:10:00 AMa19
      3/11/226:10:00 AMa20
      3/11/226:10:00 AMa21
      3/11/227:10:00 AMa22
      3/11/227:10:00 AMa23
      3/11/227:10:00 AMa24
      3/11/227:10:00 AMa25
      3/11/227:10:00 AMa26
      3/11/227:10:00 AMa27
      3/11/227:10:00 AMa28
      3/11/228:10:00 AMa29
      3/11/228:10:00 AMa30
      3/11/228:10:00 AMa31
      3/11/228:10:00 AMa32
      3/11/228:10:00 AMa33
      3/11/228:10:00 AMa34
      3/11/228:10:00 AMa35
      3/11/228:10:00 AMa36
      3/11/229:10:00 AMa37
      3/11/229:10:00 AMa38
      3/11/229:10:00 AMa39
      3/11/229:10:00 AMa40
      3/11/229:10:00 AMa41
      3/11/229:10:00 AMa42
      3/11/229:10:00 AMa43
      3/11/229:10:00 AMa44
      3/11/229:10:00 AMa45
      3/11/2210:10:00 AMa46
      3/11/2210:10:00 AMa47
      3/11/2210:10:00 AMa48
      3/11/2210:10:00 AMa49
      3/11/2210:10:00 AMa50
      3/11/2210:10:00 AMa51
      3/11/2210:10:00 AMa52
      3/11/2210:10:00 AMa52

       

      The graph should be generated based on the following data that is to be derived from the above table by aggregating the users for 1 hr interval:

      DateTime IntervalUsername (#)
      3/11/220-1AM0
      3/11/221-2AM1
      3/11/222-3AM2
      3/11/223-4AM3
      3/11/224-5AM4
      3/11/225-6AM5
      3/11/226-7AM6
      3/11/227-8AM7
      3/11/228-9AM8
      3/11/229-10AM9
      3/11/2210-11AM8
      3/11/2211-12AM7
      3/11/2212-1PM8
      3/11/221-2PM9
      3/11/222-3PM10
      3/11/223-4PM11
      3/11/224-5PM12
      3/11/225-6PM13
      3/11/226-7PM14
      3/11/227-8PM15
      3/11/228-9PM16
      3/11/229-10PM17
      3/11/2210-11PM18
      3/11/2211-12PM19
      3/12/2212-1AM20
      3/12/221-2AM19
      3/12/222-3AM18
      3/12/223-4AM17
      3/12/224-5AM16
      3/12/225-6AM15
      3/12/226-7AM14
      3/12/227-8AM13
      3/12/228-9AM12
      3/12/229-10AM11
      3/12/2210-11AM12
      3/12/2211-12AM13
      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        Your data is good as is. Add a calculated column 

         

         

        Time Interval = hour([Time])

         

         

        and put everything in a table.

         

        I didn't follow your formatting because it is ambiguous, especially the "11-12AM" part 🙂 . If you want you can add that yourself.