Forum Discussion
Average time by date
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.
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.
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.