Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.