Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
deedeedudu
Helper II
Helper II

Create a trend graph with date and time

‎03-25-2022 04:20 AM

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:

 

deedeedudu_1-1648207171163.png

 

Labels:
Message 1 of 5
742 Views
0
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support
In response to deedeedudu

‎03-29-2022 02:11 AM

Hi @deedeedudu ,

 

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] ) )
)

vzhangti_0-1648544955734.png

Index is only used for sorting.

vzhangti_1-1648544980051.png

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.

View solution in original post

Create a trend graph with date and time.pbix
Message 5 of 5
641 Views
0
4 REPLIES 4
lbendlin
Super User
Super User

‎03-26-2022 08:53 AM

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.

Message 2 of 5
698 Views
0
deedeedudu
Helper II
Helper II
In response to lbendlin

‎03-26-2022 08:12 PM

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
Message 3 of 5
682 Views
0
v-zhangti
Community Support
Community Support
In response to deedeedudu

‎03-29-2022 02:11 AM

Hi @deedeedudu ,

 

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] ) )
)

vzhangti_0-1648544955734.png

Index is only used for sorting.

vzhangti_1-1648544980051.png

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.

Create a trend graph with date and time.pbix
Message 5 of 5
642 Views
0
lbendlin
Super User
Super User
In response to deedeedudu

‎03-27-2022 05:46 PM

Your data is good as is. Add a calculated column 

 

 

Time Interval = hour([Time])

 

 

and put everything in a table.

 

lbendlin_0-1648428291884.png

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

Message 4 of 5
652 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All