Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

Show value as days and hours and aggregate

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. 


Super User
Super User

@lalTel20 , We can not use text format measure on bar visual


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

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. 



Hi @lalTel20 ,


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.

Hi @v-stephen-msft ,

sample Data - 

StoreAttributeStart DurationDCM CaseEnd DurationCalendar Duration ElapsedBusiness Duration Elapsed
BallaratDate Raised4/11/2021 16:51caseid01889645/11/2021 17:321.0284722221.028472222
BallaratStore Pickup5/11/2021 17:32caseid01889648/11/2021 9:362.6694444440.669444444
BallaratDelivered To Warehouse8/11/2021 9:36caseid018896410/11/2021 16:232.2826388892.282638889
BallaratPickup from Warehouse10/11/2021 16:23caseid018896411/11/2021 14:080.906250.90625
BallaratDelivered to Store11/11/2021 14:08caseid018896411/11/2021 14:380.0208333330.020833333
BendigoDate Raised8/11/2021 10:48caseid01914099/11/2021 15:511.2104166671.210416667
BendigoStore Pickup9/11/2021 15:51caseid019140910/11/2021 14:150.9333333330.933333333
BendigoDelivered To Warehouse10/11/2021 14:15caseid019140911/11/2021 16:331.0958333331.095833333
BendigoPickup from Warehouse11/11/2021 16:33caseid019140912/11/2021 10:370.7527777780.752777778
BendigoDelivered to Store12/11/2021 10:37caseid019140912/11/2021 11:070.0208333330.020833333


Current outcome - 


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


Hi @lalTel20 ,


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.


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors