cancel
Showing results 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

## 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

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

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.

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?

Community Support

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.

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 - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors