Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average of Datediff

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:

 

 

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, Anonymous 

     

    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

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      v-angzheng-msft 

       

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

       

      Thanks,

       

      Samuel

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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