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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.