Average of Datediff

[SamuelTesf]

Hi Everyone:

I have a pickle on my hands. The below query works if I want to get count of the column created to resolved duration.  What I am looking to get is the average for each month say October 2021, September 2021 . etc How do I do this?

Count Quary: 

Created to Resolved Duration

Count 

This is what it should look like:

[v-angzheng-msft · Accepted solution]

Hi, @SamuelTesf 

According to your screenshot above, the return result of duration is the text type, and the count is to count the number of returned text, which makes people wonder whether the correct result is obtained.
You can try to modify the above calculated column as follows:

Created to Resolved Duration = 
IF(ISBLANK('Table'[Resolve Date]),0,DATEDIFF('Table'[Create Date],'Table'[Resolve Date],DAY))

Average=
AVERAGE('Table'[Created to Resolved Duration])

If it doesn't work, then please consdier sharing more details about it or a simple sample file without any sesentive information. it makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SamuelTesf]

@v-angzheng-msft 

Thank you so much, You solved my issue. I got a feeling it was becouse of the text open. 

Thanks,

Samuel

[SamuelTesf]

Hi Everyone,

I am having issue with this Average formula when i take te same column and do count in new measure it workes perfectly but not in Average formula. What am I doing wrong? The idea is to have data on the average time it takes to resolve a ticket. 

Average Formula 

Average Created to Resolved Duration = AVERAGE('FreshService Data RAW'[Created to Resolved Duration])

Count formula

Count Created to Resolved Duration = Count('FreshService Data RAW'[Created to Resolved Duration])

Column: Created to Resolved Duraton:

Created to Resolved Duration = IF(ISBLANK('FreshService Data RAW'[Resolved Date]),"Open",FORMAT(DATEDIFF('FreshService Data RAW'[Created Date],'FreshService Data RAW'[Resolved Date],DAY), "General Number"))

What Average Created Resolved Duration column should look like

[Greg_Deckler]

@SamuelTesf Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.