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 All,
I have a dataset with the time columns: Estimated Hours, Actual Hours and Remaining Hours.
I am pulling my data from an SQL server and the time fields are pulling through as decimal data types. So time of 45 minutes is actually showing as 0.75 hours.
I'm aware that there have been similar questions in this forum and the solution that I got from them was:
Actual Hours = VAR total_time = SELECTEDVALUE('Table'[ActualHours])
VAR hrs = INT(total_time)
VAR mins = (total_time - hrs) * 60
RETURN
FORMAT( hrs, "00") & ":" & FORMAT(mins,"00")
This has worked for the columns Estimated Hours and Actual Hours as they are never negative values.
However, Remaining hours is the differrence between the above two so sometimes the this column can have negative values and whenever there is, the formula gives the incorrect value.
Below is a screenshot and the highlighted row should have -10 in there instead of -1:50. Is anyone able to suggest an alternative please?
Solved! Go to Solution.
Hi @saadat_rda ,
Here are the steps you can follow:
1. Create calculated column.
Flag =
var _leftEST=VALUE(LEFT('Table'[Estimate Hours],2))
var _leftACT=VALUE(LEFT('Table'[Actual_Hours],2))
var _midEST=VALUE(MID('Table'[Estimate Hours],4,2))
var _midACT=VALUE(MID('Table'[Actual_Hours],4,2))
var _left=
_leftEST - _leftACT
var _mid=
_midEST - _midACT
return
SWITCH(
TRUE(),
_left=0 && _mid<0, "-"&""&(_left+1)&":"&60 +_mid,
_left >=0 && _mid>=0,_left &":"&_mid,
_left >=0 && _mid<0,_left +1 &":"& 60 +_mid,
_left <0 && _mid>=0, _left&":"& _mid,
BLANK()
)
Remaining Hours =
var _len=
LEN('Table'[Flag])
var _left=LEFT('Table'[Flag],1)
return
SWITCH(
TRUE(),
_len=5&&_left="-","-0"&""&MID([Flag],2,4),
_len=3,"0"&""&[Flag]&""&"0",
_len=4,"0"&""&[Flag],
[Flag]
)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @saadat_rda ,
Here are the steps you can follow:
1. Create calculated column.
Flag =
var _leftEST=VALUE(LEFT('Table'[Estimate Hours],2))
var _leftACT=VALUE(LEFT('Table'[Actual_Hours],2))
var _midEST=VALUE(MID('Table'[Estimate Hours],4,2))
var _midACT=VALUE(MID('Table'[Actual_Hours],4,2))
var _left=
_leftEST - _leftACT
var _mid=
_midEST - _midACT
return
SWITCH(
TRUE(),
_left=0 && _mid<0, "-"&""&(_left+1)&":"&60 +_mid,
_left >=0 && _mid>=0,_left &":"&_mid,
_left >=0 && _mid<0,_left +1 &":"& 60 +_mid,
_left <0 && _mid>=0, _left&":"& _mid,
BLANK()
)
Remaining Hours =
var _len=
LEN('Table'[Flag])
var _left=LEFT('Table'[Flag],1)
return
SWITCH(
TRUE(),
_len=5&&_left="-","-0"&""&MID([Flag],2,4),
_len=3,"0"&""&[Flag]&""&"0",
_len=4,"0"&""&[Flag],
[Flag]
)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks Liu. It worked and I really appreciate this!
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |