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

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
Not applicable
6 years ago

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