Forum Discussion

Wkbdrguy's avatar
Wkbdrguy
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

Need Help with Time calculation and filters

I'm working with the following calculation to get average broken into days, hours, Minutes. TTA = Time to Acknowledge ------------------------------------ AVG TTA = VAR Dur = AVERAGE('Table'[TTA ...
Need Help
  • Wkbdrguy's avatar
    Wkbdrguy
    7 years ago

    Solved: I created 3 separate measures: 

     

    AVG TTA (Days)  =

    //CALCULATIONS

    VAR Dur = AVERAGE ( Table[TTA (Sec)] )

    //Separate Time

    VAR DAYS = IF ( Dur >= 1, INT ( Dur / 86400 ), BLANK () )

    VAR HOURS = IF ( Dur >= 1, INT ( MOD ( Dur - ( Days * 86400 ), 86400 ) / 3600 ), BLANK () )

    VAR MINUTES = INT ( MOD ( Dur - ( Hours * 3600 ), 3600 ) / 60 )

    //Format

    VAR D =

    IF ( DAYS > 1, FORMAT ( DAYS, "00" ), BLANK () )

    RETURN

    D

    AVG TTA (Hours) =

    //CALCULATIONS

    VAR Dur = AVERAGE ( Table[TTA (Sec)] )

    //Separate Time

    VAR DAYS = IF ( Dur >= 1, INT ( Dur / 86400 ), BLANK () )

    VAR HOURS = IF ( Dur >= 1, INT ( MOD ( Dur - ( Days * 86400 ), 86400 ) / 3600 ), BLANK () )

    VAR MINUTES = INT ( MOD ( Dur - ( Hours * 3600 ), 3600 ) / 60 )

    //Format

    VAR H =

    IF ( HOURS > 1, FORMAT ( HOURS, "00" ),BLANK())

    RETURN

    H

    AVG TTA (Min) =

    //CALCULATIONS

    VAR Dur = AVERAGE ( Table[TTA (Sec)] )

    //Separate Time

    VAR DAYS = IF ( Dur >= 1, INT ( Dur / 86400 ), BLANK () )

    VAR HOURS = IF ( Dur >= 1, INT ( MOD ( Dur - ( Days * 86400 ), 86400 ) / 3600 ), BLANK () )

    VAR MINUTES = INT ( MOD ( Dur - ( Hours * 3600 ), 3600 ) / 60 )

    //Format

    VAR M =

    FORMAT ( MINUTES, "00" )

    RETURN

    M

     

    After creating the 3 measures I created a Small table to display the results and the filters/slicers for different months are now working to update the average:

     

    Examples:

     

     

     

     

     

     

     

     

    Time to Activate = Time taken from ticket creation to the time someone begins working a ticket.

    Time to Close = Time taken from ticket creation to the time a ticket is closed.

Wkbdrguy's avatar
Wkbdrguy
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Figured it out. See other post.
Wkbdrguy's avatar
Wkbdrguy
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Solved: I created 3 separate measures: 

 

AVG TTA (Days)  =

//CALCULATIONS

VAR Dur = AVERAGE ( Table[TTA (Sec)] )

//Separate Time

VAR DAYS = IF ( Dur >= 1, INT ( Dur / 86400 ), BLANK () )

VAR HOURS = IF ( Dur >= 1, INT ( MOD ( Dur - ( Days * 86400 ), 86400 ) / 3600 ), BLANK () )

VAR MINUTES = INT ( MOD ( Dur - ( Hours * 3600 ), 3600 ) / 60 )

//Format

VAR D =

IF ( DAYS > 1, FORMAT ( DAYS, "00" ), BLANK () )

RETURN

D

AVG TTA (Hours) =

//CALCULATIONS

VAR Dur = AVERAGE ( Table[TTA (Sec)] )

//Separate Time

VAR DAYS = IF ( Dur >= 1, INT ( Dur / 86400 ), BLANK () )

VAR HOURS = IF ( Dur >= 1, INT ( MOD ( Dur - ( Days * 86400 ), 86400 ) / 3600 ), BLANK () )

VAR MINUTES = INT ( MOD ( Dur - ( Hours * 3600 ), 3600 ) / 60 )

//Format

VAR H =

IF ( HOURS > 1, FORMAT ( HOURS, "00" ),BLANK())

RETURN

H

AVG TTA (Min) =

//CALCULATIONS

VAR Dur = AVERAGE ( Table[TTA (Sec)] )

//Separate Time

VAR DAYS = IF ( Dur >= 1, INT ( Dur / 86400 ), BLANK () )

VAR HOURS = IF ( Dur >= 1, INT ( MOD ( Dur - ( Days * 86400 ), 86400 ) / 3600 ), BLANK () )

VAR MINUTES = INT ( MOD ( Dur - ( Hours * 3600 ), 3600 ) / 60 )

//Format

VAR M =

FORMAT ( MINUTES, "00" )

RETURN

M

 

After creating the 3 measures I created a Small table to display the results and the filters/slicers for different months are now working to update the average:

 

Examples:

 

 

 

 

 

 

 

 

Time to Activate = Time taken from ticket creation to the time someone begins working a ticket.

Time to Close = Time taken from ticket creation to the time a ticket is closed.