Forum Discussion

Matic20's avatar
Matic20
Frequent Visitor
7 years ago
Solved

Date and Time Difference

Hello  team  I have there Columns, Status ( this includes things like pause, resume, close, create, postreply), TicketID(primary key ) and Log Status Creation( time for every status). I need to calc...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Matic20 ,

    For your requirement, you could create the measure below.

    difference =
VAR pauseticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" )
    )
VAR resumeticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" )
    )
VAR diff =
    DATEDIFF ( pauseticket, resumeticket, SECOND )
VAR hours =
    INT ( diff / 3600 )
VAR minutes =
    INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 )
RETURN
    hours & "hr" & minutes & "mints"

    Here is the output.

    Hope this can help you.

    Best  Regards,

    Cherry

     

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Matic20 ,

For your requirement, you could create the measure below.

difference =
VAR pauseticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" )
    )
VAR resumeticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" )
    )
VAR diff =
    DATEDIFF ( pauseticket, resumeticket, SECOND )
VAR hours =
    INT ( diff / 3600 )
VAR minutes =
    INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 )
RETURN
    hours & "hr" & minutes & "mints"

Here is the output.

Hope this can help you.

Best  Regards,

Cherry

 

Matic20's avatar
Matic20
Frequent Visitor
7 years ago
v-piga-msft wrote:

Hi Matic20 ,

For your requirement, you could create the measure below.

difference =
VAR pauseticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" )
    )
VAR resumeticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" )
    )
VAR diff =
    DATEDIFF ( pauseticket, resumeticket, SECOND )
VAR hours =
    INT ( diff / 3600 )
VAR minutes =
    INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 )
RETURN
    hours & "hr" & minutes & "mints"

Here is the output.

Hope this can help you.

Best  Regards,

Cherry

 

Thank you so much, this really did help