Heatmap Formatting - Display Days and Hours (Custom Axis Labels)

[manoj_0911]

Hi everyone,

I'm working on a Heatmap in Power BI to visualize total calls offered by day. However, I'm facing an issue with the axis labels.

Currently, the rows display sequential numbers (1, 2, 3, ...) representing days, and the columns display numeric hours (0, 1, 2, ..., 23).

Instead, I'd like to display the days of the week (Sun, Mon, Tue, ...) in the rows and hours in a 12-hour format (01AM, 02AM, ..., 11PM) in the columns.

Here's how I've defined my measures so far:

```
Day_of_Week_Order = SWITCH(
'AGENT_SUBHOUR_V'[Day_of_Week],
"Sunday", 1,
"Monday", 2,
"Tuesday", 3,
"Wednesday", 4,
"Thursday", 5,
"Friday", 6,
"Saturday", 7,
0
)

Hour_of_Day = HOUR('AGENT_SUBHOUR_V'[IXN_SUBHOUR_DATE])
```

Values = Sum of Offered

Can anyone advise on how to customize the axis labels to display the desired format (day of week and 12-hour hours)?

Thank you in advance for your help!

[v-xinruzhu-msft]

Hi @manoj_0911 

As @Zang_Mi  mentioned, you need to format the date, you can create two calculated columns

Weekday = FORMAT([IXN_SUBHOUR_DATE],"DDD")

Hour = FORMAT([IXN_SUBHOUR_DATE],"hh AM/pm")

Output

Best Regards!

Yolo Zhu

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

[manoj_0911]

Hi , How to sort the weekdays as like Sun, Mon, Tue, Wed, Thur, Fri and Saturday

[v-xinruzhu-msft]

Hi @manoj_0911 

You can add a calculated column

WeekNo = WEEKDAY([IXN_SUBHOUR_DATE])

Then click the weekday column,select sort by 'WeekNo'

Output

Best Regards!

Yolo Zhu

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

[manoj_0911]

Sorry, I missed adding this info before also I want the hours in this order 12am,1am,2am,3am,4am,5am,6am,7am,8am,9am,10am,11am,12pm,1pm,2pm,3pm,4pm,5pm,6pm,7pm,8pm,9pm,10pm,11pm

[v-xinruzhu-msft · Accepted solution]

Hi @manoj_0911 

It is the same step as before.

Create a claulated column

HourNo = HOUR([IXN_SUBHOUR_DATE])

Then sort it ascendng, then select the hour column, select sort by HourNo

Best Regards!

Yolo Zhu

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

[manoj_0911]

Hi I need one help, I have a field , 

X-AXIS = MonthYear = FORMAT('AGENT_SUBHOUR_V'[IXN_SUBHOUR_DATE], "MMM-YYYY") and Y-AXIS = OFFERED.  the sorting of month-year is not proper, how to create a MonthYearNo for this?

[manoj_0911]

Fantastic thank you so much it worked

[Zang_Mi]

Hello,

What you need is to use a calculated column or a column from dimension table with the desired format of days and hours in the visual.

Regards