Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Best Method to calculate the difference between two date/times columns across multiple tables

Been trying to accurately calculate the date difference between two difference dates and times from two different columns.   I have a table of Job Ids, which has the created on date column for that...
data protection
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    hi,

     

    According to your description, please try this measure:

    Time taken = DIVIDE(DATEDIFF(Job[createdon],SUMX(RELATEDTABLE(slakpiinstances),slakpiinstances[Responsetime]),SECOND),60)

    You can also use ‘Fixed’ to set how many decimals you want.

    Here is my test table:

    And remember to click ‘Show data with no item’ to shows the blank rows.

    The result shows:

    Here is my test pbix file.

    pbix 

    If you still have questions about it, please for free to let me know.

     

    Best Regards,

    Giotto Zhi

v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

hi,

 

According to your description, please try this measure:

Time taken = DIVIDE(DATEDIFF(Job[createdon],SUMX(RELATEDTABLE(slakpiinstances),slakpiinstances[Responsetime]),SECOND),60)

You can also use ‘Fixed’ to set how many decimals you want.

Here is my test table:

And remember to click ‘Show data with no item’ to shows the blank rows.

The result shows:

Here is my test pbix file.

pbix 

If you still have questions about it, please for free to let me know.

 

Best Regards,

Giotto Zhi

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you