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: Date Time Username 3/11/2022 7:19:16 AM abc 3/13/2022 11:20:56 PM xyx 3/13/2022 12:25:44 PM fff  ...
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.

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.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    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.