cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srivallyK_14
Helper I
Helper I

Display time and date in x-axis

Hi
 

I have a field DateTime, Sales and Customer. Here I want to show time range like 12AM-6AM, 6AM-12PM, 12PM-6PM,6PM-12AM in axis along with date means I have to see at which time range sales are happening for all the dates. Can anyone help me on it.

TIA.

Regards,
Srivalli

1 ACCEPTED SOLUTION
timg
Solution Specialist
Solution Specialist

Hi Srivally,

You could achieve this by either making a calculated column. Below you'll find an example. If you want to customize the labels such as in the calculated column example, keep in mind that you will also need to add a second calculated column in order to rank the categories correctly (since the custom labels will be text and therefore ordered alphabetically). Below you'll also find an example of how you can make a sorting column.

TimeBucket = 
SWITCH (
    TRUE (),
    HOUR ( 'public click'[utcTimeStamp] ) IN { 0, 1, 2, 3, 4, 5 }, "12AM - 6AM",
    HOUR ( 'public click'[utcTimeStamp] ) IN { 6, 7, 8, 9, 10, 11 }, "6AM - 12PM",
    "ETC."
)
TimeBucketRank = 
SWITCH (
    TRUE (),
    HOUR ( 'public click'[utcTimeStamp] ) IN { 0, 1, 2, 3, 4, 5 }, 1,
    HOUR ( 'public click'[utcTimeStamp] ) IN { 6, 7, 8, 9, 10, 11 }, 2,
    3
)

1.PNG

An alternative would be to create a group with a 6 hour interval size. However, this method does give you fixed labels so you would be required to change these afterwards if you want custom labels 

2.PNG

 

Best regards,

 

Tim

View solution in original post

1 REPLY 1
timg
Solution Specialist
Solution Specialist

Hi Srivally,

You could achieve this by either making a calculated column. Below you'll find an example. If you want to customize the labels such as in the calculated column example, keep in mind that you will also need to add a second calculated column in order to rank the categories correctly (since the custom labels will be text and therefore ordered alphabetically). Below you'll also find an example of how you can make a sorting column.

TimeBucket = 
SWITCH (
    TRUE (),
    HOUR ( 'public click'[utcTimeStamp] ) IN { 0, 1, 2, 3, 4, 5 }, "12AM - 6AM",
    HOUR ( 'public click'[utcTimeStamp] ) IN { 6, 7, 8, 9, 10, 11 }, "6AM - 12PM",
    "ETC."
)
TimeBucketRank = 
SWITCH (
    TRUE (),
    HOUR ( 'public click'[utcTimeStamp] ) IN { 0, 1, 2, 3, 4, 5 }, 1,
    HOUR ( 'public click'[utcTimeStamp] ) IN { 6, 7, 8, 9, 10, 11 }, 2,
    3
)

1.PNG

An alternative would be to create a group with a 6 hour interval size. However, this method does give you fixed labels so you would be required to change these afterwards if you want custom labels 

2.PNG

 

Best regards,

 

Tim

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors