Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there
I'm looking to do a formula that calculates hours to days, hours and minutes, but it's a little wonky and not quite straight forward.
There's are two type of work days - one is 9 hours and the other is 7hrs 30 mins.
Workday Type 1 = 7hrs 30
Workday Type 2 = 9
Data looks a little like:
Name | Project | Hours | WorkDay Type |
Person 1 | 123 | 03:45 | 1 |
Person 1 | 852 | 08:00 | 2 |
Person 1 | N/A | 00:30 | N/A |
Person 2 | 123 | 08:15 | 1 |
Person 2 | 741 | 02:50 | 2 |
Person 3 | 852 | 03:00 | 2 |
So what I'd expect from this would be:
Project 123: (12 Hours) 1 Day, 4 Hours, 30 Mins
Project 741: (2 Hours) 2 Hours
Project 852: (11 Hours) 1 Day, 2 Hours
And then, if possible, in another formula, to translate that to decimal days (rounding up/down as needed), so using the examples above, I'd expect:
Project 123: 1.75 (1 full day and roughly about 3/4ths of a day)
Project 741: 0.15 (nearly qtr of a day)
Project 852: 1.15 (I think that's right, my maths isn't the best, sorry)
Thank you!!
@smather , First assume you have measure sim of hours
Total = sum(Table[Hours])
or
Total = sumX(Table, Hour([Time]) + Minute([Time])/60)
Then you can have the measure
Total Decimal = Quotient([Total ],8) +MoD([Total ],8)/8
Total Text= Quotient([Total ],8) & " Days " & MoD([Total ],8)/8 & " Hours"
Hi @amitchandak
Yes, I already have a Sum(Table[hours]) 🙂
Is the 8 in your formula the working day? Because depending on the type of workday, it could either be 7h30m or 9h - could you explain it a little more for me please?
Hi @smather ,
You can use SWITCH() to change the denominator where is 8 in that formula.
SWITCH( SELECTEDVALUE( 'Table'[WorkDay Type]),
1, 7.5,
2, 9 )
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |