Is Time calculation using measures possible?

[Anonymous]

Hi, 

I currently have this measure 

FTE = FORMAT((COUNT('Processing_Log'[Sl. No])) * (TIME(00,12,00) - (AVERAGE('Processing_Log'[Total time Taken]))) , "HH:MM:SS")

I'm trying to subtract 12 minutes from an average of the total time taken which is in HH:MM: SS format. and multiplying with the total number of rows I have. I have to depict this in a  card visualization.

I'm able to achieve it (I guess) but after the cumulative sum of 24 hours, the time is reset to 00:00:00 I want the card to depict more than 24 hours of cumulative data too. 

 

Can you guys please help? Please. 

[JarroVGIT · Accepted solution]

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 🙂

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639?search-a...

[JarroVGIT · Accepted solution]

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 🙂

[Anonymous]

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.?

@JarroVGIT ?

[JarroVGIT]

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 + tttM2S

tttSeconds 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 🙂

[Anonymous]

Hey @JarroVGIT 

Executed your query as mentioned above but, I'm getting the solution as,

but FTE calculation goes like this

=596 * [(12*60) - ((3*60)+32)]

=596[720-212]~ 302768 sec ~ 84hours.

The data isn't matching. please help.. 😞

Hope I haven't messed up during implementation

[Anonymous · Accepted solution]

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) * 3600

VAR tttM2S = MINUTE(tttAVG) * 60

VAR tttSeconds = SECOND(tttAVG) + tttH2S + tttM2S

VAR 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")

[JarroVGIT]

Good to hear! I see my reply didn't come through with a approach to your last question, apologies!