topic Re: undefined in DAX Commands and Tips

undefined

AtchayaP — Tue, 08 Feb 2022 15:43:46 GMT

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

Re: undefined

Whitewater100 — Tue, 08 Feb 2022 16:03:58 GMT

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.

Re: undefined

AtchayaP — Tue, 08 Feb 2022 20:42:37 GMT

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

Re: undefined

v-jingzhang — Fri, 11 Feb 2022 07:08:43 GMT

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.