Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Average time by date

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

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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
Not applicable

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!

Community Support

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.

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors