Average time by date

[Anonymous]

Hello! I have a table with Date and Time spent for activity (1 line – 1 activity). I need to calculate the Average time for 1 activity by Date (starting from the second activity, because I don’t know the time for the 1st activity). Could you please recommend a measure for this?

See attached my data set in Excel. On the tab 142022 I calculated it manually for 1/4/2022. https://docs.google.com/spreadsheets/d/1mCPyykCgNxYFSvp2OZy1tw7C-dcMnwTM/edit#gid=575093181 

[v-xinruzhu-msft]

Hi @Anonymous 

You can refer to the following  measure

Measure = var a=MAXX(FILTER(ALLSELECTED('Table'),[Time]<SELECTEDVALUE('Table'[Time])),[Time])
var b=SELECTEDVALUE('Table'[Time])-a
return IF(a<>BLANK(),MINUTE(b)*60+SECOND(b))

Measure 2 = var a=SUMMARIZE(ALLSELECTED('Table'),[Date],[Time],"Spent",[Measure])
return AVERAGEX(FILTER(a,[Date] in VALUES('Table'[Date])&&[Time] in VALUES('Table'[Time])),[Spent])

Output

I translate it to seconds

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Yolo Zhu! That's amazing! Thank you so much! But unfortunately I couldn't implement it to all the data set. See attached my pbix https://drive.google.com/file/d/1BbgXO0TOL6gg5YhtmxuJ-apslLTz6-Zu/view?usp=drive_link

Acctually, I need a table like this:

Date	Seconds
1/4/2022	135.74
1/5/2022	140.2
1/7/2022	200
1/9/2022	131.5
2/4/2022	150
…	…
4/18/2023	154.5

Do you know what can be the reason?

I do appreciate your help!

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

Try to create a calculated column first

Column = var a=MAXX(FILTER(DateTime,[Date]=EARLIER(DateTime[Date])&&[Time]<EARLIER(DateTime[Time])),[Time])
return IF(a<>BLANK(),MINUTE([Time]-a)*60+SECOND([Time]-a))

Then create the meausre

Measure = AVERAGE(DateTime[Column])

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.