Forum Discussion
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...
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.
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!
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.