cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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

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

#### Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors