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.

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