Forum Discussion
Is Time calculation using measures possible?
Hi,
I currently have this measure
Hi Anonymous ,
The outcome you have is because TIME() returns a datetime value, not a duration value. A datetime value reflects the timestamp of a moment, while a duration reflects the amount of time something takes. The notation can be similar (e.g. HH:MM:SS) but the datatypes are very different.
In your case, I would recommend to calculate everything in seconds and then revert it to a HH:MM:SS format.
FTE = VAR howlongItTook = COUNT('Processing_Log'[Sl. No])) * (12*60) VAR avgTimeTaken = AVERAGE('Processing_Log'[Total time Taken]) //Depending on your datastructure, avgTimeTaken would need to be converted to seconds. VAR fteSeconds = howlongItTook - avgTimeTaken VAR Hr = INT(fteSeconds/3600) VAR Mi = INT((fteSeconds - (Hr *3600))/60) VAR Se = MOD(fteSeconds, 60) RETURN Hr&":"&FORMAT(Mi,"00")&":"&FORMAT(Se,"00")Something like this. Note that this wouldn't work right away, see comment after VAR avgTimeTaken. You will need to calculate the average of the [Total time Taken] column in seconds, but you get the idea.
Hope this helps!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
- Small Changes in the code I could get the count. Thanks.FTE =VAR avgTimeTaken = AVERAGE('Processing_Log'[Total time Taken])VAR tttAVG = FORMAT(AVERAGE('Processing_Log'[Total time Taken]), "HH:MM:SS")VAR tttH2S = HOUR(tttAVG) * 3600VAR tttM2S = MINUTE(tttAVG) * 60VAR tttSeconds = SECOND(tttAVG) + tttH2S + tttM2SVAR fteSeconds = (COUNT(Processing_Log[Request Status]) * ( (12*60) - tttSeconds))VAR Hr = INT(fteSeconds/3600)VAR Mi = INT((fteSeconds - (Hr *3600))/60)VAR Se = MOD(fteSeconds, 60)RETURN Hr&":"&FORMAT(Mi,"00")&":"&FORMAT(Se,"00")
7 Replies
Hi Anonymous ,
The outcome you have is because TIME() returns a datetime value, not a duration value. A datetime value reflects the timestamp of a moment, while a duration reflects the amount of time something takes. The notation can be similar (e.g. HH:MM:SS) but the datatypes are very different.
In your case, I would recommend to calculate everything in seconds and then revert it to a HH:MM:SS format.
FTE = VAR howlongItTook = COUNT('Processing_Log'[Sl. No])) * (12*60) VAR avgTimeTaken = AVERAGE('Processing_Log'[Total time Taken]) //Depending on your datastructure, avgTimeTaken would need to be converted to seconds. VAR fteSeconds = howlongItTook - avgTimeTaken VAR Hr = INT(fteSeconds/3600) VAR Mi = INT((fteSeconds - (Hr *3600))/60) VAR Se = MOD(fteSeconds, 60) RETURN Hr&":"&FORMAT(Mi,"00")&":"&FORMAT(Se,"00")Something like this. Note that this wouldn't work right away, see comment after VAR avgTimeTaken. You will need to calculate the average of the [Total time Taken] column in seconds, but you get the idea.
Hope this helps!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Thank you soo much ... worked like a charm.
i just had a small query of the average time.. my total time taken column contains data in HH : MM : SS format as well. which contains data both in Minutes and seconds. how do i convert it? how do you suggest i move forward.?
You could try something like this:
VAR tttAVG = AVERAGE('Processing_Log'[Total time Taken]) VAR tttH2S = HOUR(tttAVG) * 3600 VAR tttM2S = MINUTE(tttAVG) * 60 VAR tttSeconds = SECOND(tttAVG) + tttH2S + tttM2StttSeconds will now hold the average time in seconds 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
