Avg Journey Times Duration in hh:mm:ss

[Jeev]

Hi All, I have a data set for duration in hh:nn:ss format in my data table. But I am not able to do avg  for each month. If i do avg i get in whole number format or genral format which i dont want.

What i need,

I need to get Avg duration for month in hh:nn:ss format. Below is the raw data image. Please let me know.

Show the below in Graphical Format or tabular.

June Avg= Average(june duration) 

Aug Avg= Average (august Duration )

Raw Data Image

[v-kelly-msft · Accepted solution]

Hi @Jeev ,

First create 2 columns as below:

Minutes = 
var _minute=MINUTE('Table'[JT Avg])
var _sec=SECOND('Table'[JT Avg])
Return
_minute+_sec/60

Then create a measure as below:

Measure 2 = 
var _avermin=AVERAGEX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),'Table'[Minutes])
var _aversec=ROUND(_avermin-ROUNDDOWN(_avermin,0),2)*100
Return
"00:"&CONCATENATE(CONCATENATE(ROUNDDOWN(_avermin,0),":"),ROUND(_aversec,0))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@Jeev - See this Quick Measure - https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

Also this article from ages ago: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

[amitchandak]

@Jeev , For Duration refer

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

For this month Vs last month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

refer :  https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e