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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
jgulliver
Frequent Visitor

Calculate the average of Time to First Action (which is in text format)

Hi,

 

I'm going to try and post a separate one as the previous post may no longer prominent and would like to add a bit more context with the scenario I am seeking some help please.

 

I have a couple of dashboards that are sourcing data from reports from our CRM. One of which is called a Leads report that gives me the Time to First Action, which is the calculation based from when the lead was created in the system to when it was flagged as beinc completed (when an agent had their initial contact with the client). I do have a Created Date and a Completed Date along with a few other information and the column that I am referring to is called Time to First Action, which is displayed in this format 12h 18m as an example (in a Text data type). I also have ones where it has been reflected in days, e.g., 1d 17h.

 

I have been asked to provide an monthly average on the Time to First Action but my issue is I'm stuck on where to begin as I am no expert and in addition, the Data Type cannot be changed to a number-related data type and this column has a combination of hours, minutes and days. I'd like to highlight that the Created Date and Completed Date are just dates (with the day) and do not include a time, so they are displayed as just dates, e.g. Tuesday, 28 November 2022.

 

Any insights anyone can provide will be extremely appreciated. I can also provide additional information where needed.

 

Many thanks!

 

2 REPLIES 2
jgulliver
Frequent Visitor

Thanks @amitchandak. I can share a sample data as attached but I have no sample output as this is something I haven't been able to create a PowerBI tile for. Hope the sample will suffice.Lead.JPG

amitchandak
Super User
Super User

@jgulliver ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

First/last nonblank value can help

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

 

In this one, it is max, you might need both min/min

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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