Forum Discussion

Saxon202202's avatar
Saxon202202
Helper III
3 years ago

Average

I have the following three column are Date@Time, Time1 and Time2 in direct query. I created new column based on the Time1. The columns name is Hour. I have two requirements: 1.  I want to create ti...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Saxon202202 Right, so you need to convert both of the times to seconds. You can then take the difference in seconds between the two and then convert back to duration format. If you post your data as text that I can copy I can be more specific and give you a full solution.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Try these 2 calculated column formulas

    Column = if('Table'[Time2]=BLANK(),BLANK(),'Table'[Time2]-'Table'[Time1])
    Column 2 = CALCULATE(AVERAGE('Table'[Column]),FILTER('Table','Table'[Hour]=EARLIER('Table'[Hour])))

    Hope this helps.

    • Saxon202202's avatar
      Saxon202202
      Helper III
      3 years ago

      Ashish_Mathur, Thanks for your reply. I am looking for measures instead of calculated column because calculated column not supporting in direct query.

      Thank you.