Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Time not shown properly

Hi guys, I  need to display time as a whole number in order to display it in the graph and therefore this is the code I use:(Below). However, somehow, when I take the filter and seperate such time by months or year, I get that it does not round up up time properly, etc. shows 79 minutes for example. 
Do you have any ideas how can I adjust this code 
 
 

 


OverTimee Converted =

 

VAR Duration =

 

SUM ('Krauti Kalij (2)'[OverTime]) // There are 3,600 seconds in an hour

 

VAR Hours =

 

INT ( 'Krauti Kalij (2)'[OverTime]/ 3600 ) // There are 60 seconds in a minute

 

VAR Minutes =

 

INT ( MOD( 'Krauti Kalij (2)'[OverTime] - ( Hours * 3600 ), 3600 ) / 60 )

 

VAR Seconds =

 

ROUNDUP ( MOD ( MOD ( 'Krauti Kalij (2)'[OverTime]- ( Hours * 3600 ), 3600 ), 60 ), 0 )

 

RETURN

 

Hours * 100 + Minutes
Need Help
Show and Tell
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

    I suggest to use measure rather than calculated column to get the result. Since you want to calculate the average delay, get the average delay in seconds first, then change the format. Here is the PBIX file.

    Average Delay = 
VAR averageDelay =
    ROUNDUP ( AVERAGE ( Sheet1[delay] ), 0 )
VAR Hours =
    INT ( averageDelay / 3600 )
VAR Minutes =
    INT ( ( averageDelay - Hours * 3600 ) / 60 )
VAR Seconds = averageDelay - Hours * 3600 - Minutes * 60
RETURN
    Hours * 100 + Minutes

    Kindly let me know if this helps.
    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.

7 Replies