topic Re: Show value as days and hours and aggregate in DAX Commands and Tips

Show value as days and hours and aggregate

Anonymous — Wed, 17 Nov 2021 07:39:10 GMT

So i'm trying to display the below value in the format "x days, y hours" but when i change the format, it does not aggregate being a text output. Has anyone worked on this? Can we have the value in the background but still display as human readable format of days and hours.

Re: Show value as days and hours and aggregate

amitchandak — Wed, 17 Nov 2021 10:09:58 GMT

Anonymous , We can not use text format measure on bar visual

try of you can give format here - property under data model

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

Re: Show value as days and hours and aggregate

Anonymous — Thu, 18 Nov 2021 00:50:29 GMT

Thankyou amitchandak for your suggestions. I have now tried this out. Only challenge now is the fractional day doesnt get converted to hours, but only shows as fractional hours.
so 0.83 days i am expecting it to convert to 20 hours.

The other alternative I tried was splitting hours in days and hours

var days = INT(total_hours/24)

var hours = MOD(total_hours,24)/100 and adding them up to show the correct days and hours. However as expected they wouldnt add up to the totals.

Re: Show value as days and hours and aggregate

Anonymous — Tue, 23 Nov 2021 02:59:05 GMT

Hi Anonymous ,

What's your data model? Can you provide me with some dummy data and expected results?

Best Regards,

Stephen Tao

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

Re: Show value as days and hours and aggregate

Anonymous — Thu, 25 Nov 2021 02:03:00 GMT

Hi Anonymous ,

sample Data -

Store	Attribute	Start Duration	DCM Case	End Duration	Calendar Duration Elapsed	Business Duration Elapsed
Ballarat	Date Raised	4/11/2021 16:51	caseid0188964	5/11/2021 17:32	1.028472222	1.028472222
Ballarat	Store Pickup	5/11/2021 17:32	caseid0188964	8/11/2021 9:36	2.669444444	0.669444444
Ballarat	Delivered To Warehouse	8/11/2021 9:36	caseid0188964	10/11/2021 16:23	2.282638889	2.282638889
Ballarat	Pickup from Warehouse	10/11/2021 16:23	caseid0188964	11/11/2021 14:08	0.90625	0.90625
Ballarat	Delivered to Store	11/11/2021 14:08	caseid0188964	11/11/2021 14:38	0.020833333	0.020833333
Bendigo	Date Raised	8/11/2021 10:48	caseid0191409	9/11/2021 15:51	1.210416667	1.210416667
Bendigo	Store Pickup	9/11/2021 15:51	caseid0191409	10/11/2021 14:15	0.933333333	0.933333333
Bendigo	Delivered To Warehouse	10/11/2021 14:15	caseid0191409	11/11/2021 16:33	1.095833333	1.095833333
Bendigo	Pickup from Warehouse	11/11/2021 16:33	caseid0191409	12/11/2021 10:37	0.752777778	0.752777778
Bendigo	Delivered to Store	12/11/2021 10:37	caseid0191409	12/11/2021 11:07	0.020833333	0.020833333

Current outcome -

need to update the days value from decimal format such as 6.375 days --> 6 days 9 Hrs

Re: Show value as days and hours and aggregate

Anonymous — Wed, 08 Dec 2021 03:04:36 GMT

Hi Anonymous ,

Sorry for my late reply.

Below is how I show you how to display the change from 6.375 days to 6 days 9 Hrs.

First, create a calculated column as follows.

DATEDIFF is to get the hours of the time difference. Divide the number of hours calculated by 24 to get the number of days, and then multiply by 100 plus the remainder of the number of hours divided by 24.

For example, _hours=153 → 609

DayHours = var _hour=DATEDIFF([Start Duration],[End Duration],HOUR) return INT(DIVIDE(_hour,24))*100+MOD(_hour,24)/100

And then create a custom format like

Now, "609" is changed to "6Days09Hrs".

"None" should be seleceted in Display units.

Best Regards,

Stephen Tao

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