Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Saxon202202
Helper III
Helper III

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 time differece in-between Time2 and Time1 in time format. (HH:MM:SS)
2.  I want to create average each line item based on the (point 1 result measure) time difference according to the date&time and Hour columns.

3. Grand Total of Time difference.

4. Grand Total of Measure.
Can you please suggest the solution for direct query menthod becuase those three columns are in DQ

Desired result look like.

 

Saxon202202_2-1674234378565.png

PBI file attached 
https://www.dropbox.com/s/tfcipx08wh6h7sc/Average-Measure.pbix?dl=0

6 REPLIES 6
Greg_Deckler
Super User
Super User

@Saxon202202 You will need these:

(1) Duration to Seconds Converter - Microsoft Power BI Community

Chelsie Eiden's Duration - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler ,
Thanks for your reply. Can you please help me becuase I am struggling to get the difference in-between two times. (Time2-Time1)
The article is explained single time column but I want to get the time difference from Time2 and Time and how can I get the average.
Can you please help me.

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler ,
Thanks for your reply. Now, I convert the both times to seconds and finout the time difference in seconds. I try to convert the time difference measure into duration but result is 00:00:00.

Saxon202202_0-1674291795992.png

Saxon202202_3-1674292902441.png

Attached the csv and PBI file.  
https://www.dropbox.com/s/tfcipx08wh6h7sc/Average-Measure.pbix?dl=0
https://www.dropbox.com/s/mf04tzid2wub1ay/Time%20conversation.csv?dl=0



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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.