Forum Discussion
Hours Subtraction
Hi there,
My formula
(NETWORKDAYS("9/6/2020","30/06/2020", Holidays)-1)*10/24)+ TIME(18,0,0) - TIME(14:0:0))
Comes out at 6.416 on excel. When formatted to [h]:mm it displays as expected 154:00.
My problem is my formula comes out as a date in Power BI, but even when I use the FORMAT function for [h]:mm, it still isnt getting anywhere near the 154:00.
Ive read other threads but really cant seem to grasp converting it.
CALCULATE(SUM(vwDimDate[WorkingDayFlag]), DATESBETWEEN(vwDimDate[Date],MAX('Room Utilisation'[New Start Date]),
[ParameterEndDate]))-1 *10/24 + TIME(18,0,0) - MAX('Room Utilisation'[Start Time, ])
Ive attached a sample, the measure is called 'Time Measure' and I'm looking replicate the excel result if that's possible.
https://1drv.ms/u/s!AtcnGX-0tS5riTFWkcV5hyHX_BKO?e=pORfMb
Thanks
Hi Anonymous ,
It seems that the a time expression with TIME function is unsupported to calculate in DATESBETWEEN function, since it's syntax is like DATESBETWEEN(<dates>, <start_date>, <end_date>) , which only accepts a date expression for the second and third argument.
CALCULATE(SUM(vwDimDate[WorkingDayFlag]), DATESBETWEEN(vwDimDate[Date],MAX('Room Utilisation'[New Start Date]),
[ParameterEndDate]))-1 *10/24 + TIME(18,0,0) - MAX('Room Utilisation'[Start Time, ])
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- amitchandak
Super User
Anonymous , for this you need to create it like text
refer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
Hi amitchandak ,
Many thanks for that. Ive realised its this part of the calculation below.
TIME(18,0,0) - TIME(14,0,0)The result is coming out as 30/12/1899 04:00:00, and it wont let me change it to anything but date/time. Is there a way around this?If the only way is to format to text, is there a way to do a calculation?Hi amitchandak ,
In short I need to get from 6.416666667 to 154:00:00 ([h]:mm:ss), which excel does.
Ive looked at many threads but nothing is equaling it.
Thanks
v-xicaiCommunity Support
Hi Anonymous ,
It seems that the a time expression with TIME function is unsupported to calculate in DATESBETWEEN function, since it's syntax is like DATESBETWEEN(<dates>, <start_date>, <end_date>) , which only accepts a date expression for the second and third argument.
CALCULATE(SUM(vwDimDate[WorkingDayFlag]), DATESBETWEEN(vwDimDate[Date],MAX('Room Utilisation'[New Start Date]),
[ParameterEndDate]))-1 *10/24 + TIME(18,0,0) - MAX('Room Utilisation'[Start Time, ])
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
