Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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. 
 
lalTel20_0-1637134656813.png

 

5 REPLIES 5
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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. 

lalTel20_0-1637196143067.png

 

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.

Anonymous
Not applicable

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 - 

lalTel20_0-1637799806331.png

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

 

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

vstephenmsft_0-1638932292848.png

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

vstephenmsft_1-1638932300950.png

"None" should be seleceted in Display units.

vstephenmsft_2-1638932315657.png

 

 

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

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors