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

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.

Top Solution Authors