Forum Discussion

AtchayaP's avatar
AtchayaP
Helper V
4 years ago
Solved

undefined

How to find time spent to solve assignment 1 and assignment 2.

  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi AtchayaP 

     

    Ensure [Time created] and [Time closed] columns are both of Time data type, then you can create a new column with below DAX. This returns spent time in seconds for every row. 

    Time in Second = DATEDIFF('Table'[Time created],'Table'[Time closed],SECOND)

     

    Then create a measure to calculate the total of time spent. I format it to show result like "xx h xx m xx s".

    Time Spent = 
VAR _totalSeconds = SUM('Table'[Time in Second])
VAR _hours = INT(_totalSeconds/3600)
VAR _minutes = INT(MOD(_totalSeconds,3600)/60)
VAR _seconds = MOD(MOD(_totalSeconds,3600),60)
RETURN
_hours&"h"&_minutes&"m"&_seconds&"s"

     

    If you drag [Time in Second] column into a card visual directly and select SUM aggregation type for it, it will show the total time spent in seconds. It equals to above result. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hello:

    You can solve this in Power Query:

    Add new column > custom column > enter this formula: Duration.TotalSeconds([End Time]-[Start Time])
    This will return result in total difference in seconds.
    For difference in minutes this is the formula: Duration.TotalMinutes([End Time]-[Start Time])

    Convert data type to decimal as the answer first ocomes out as text.

    • AtchayaP's avatar
      AtchayaP
      Helper V
      4 years ago

      I got 10457854:14:00  in card visual.should I format this?

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi AtchayaP 

     

    Ensure [Time created] and [Time closed] columns are both of Time data type, then you can create a new column with below DAX. This returns spent time in seconds for every row. 

    Time in Second = DATEDIFF('Table'[Time created],'Table'[Time closed],SECOND)

     

    Then create a measure to calculate the total of time spent. I format it to show result like "xx h xx m xx s".

    Time Spent = 
VAR _totalSeconds = SUM('Table'[Time in Second])
VAR _hours = INT(_totalSeconds/3600)
VAR _minutes = INT(MOD(_totalSeconds,3600)/60)
VAR _seconds = MOD(MOD(_totalSeconds,3600),60)
RETURN
_hours&"h"&_minutes&"m"&_seconds&"s"

     

    If you drag [Time in Second] column into a card visual directly and select SUM aggregation type for it, it will show the total time spent in seconds. It equals to above result. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.