Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Is Time calculation using measures possible?

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...
Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    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's avatar
    Anonymous
    6 years ago
    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's avatar
JarroVGIT
Resident Rockstar
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
      JarroVGIT
      Resident Rockstar
      6 years ago
      Good to hear! I see my reply didn't come through with a approach to your last question, apologies!