Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
6 years ago

Average difference date value is different in power BI

Hi there,

 

My sample data is present in this link: https://1drv.ms/x/s!AucycxZHFe9TjTF2d36VklLNs7UN?e=gOenxH . I am trying to calculate the average difference between the installationdate column present in the file and today(). Here is my code for calculating:

 

Average conductor life = CALCULATE(
AVERAGEX(conduction_info,DATEDIFF(conduction_info[INSTALLATIONDATE],TODAY(),YEAR)),
FILTER(conduction_info,conduction_info[TYPE]="OH")
)
 
Above formula give me 40 as the result. However, when I calculate the average difference on excel, it gives 45. Could anyone please guide me where am I making the mistake?
Need Help
Show and Tell
Tips and Tricks

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Your 40 result looks correct to me in both PBI and Excel.  What formula are you using in Excel?

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Dunner2020's avatar
      Dunner2020
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      mahoneypat ,

       

      That's wired. I use the following approach.

      First I create new column using following formula.

      =DATEDIF(A2,TODAY(),"Y")

      Then I calculate the average using Average() function to calculate the average value of new column. 

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    In cell D2, enter this formula and copy down

    =DATEDIF(A2,TODAY(),"y")

    Now if you average this column, the result is 40.  So your DAX formulas is returning the correct result.

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Dunner2020,

    Did these reply help with your scenario?
    If this is a case, you can accept the most suitable one as a solution to help other users who faced the same issue. If not, please feel free to post here with detailed information.

    How to Get Your Question Answered Quickly 
    Regards,

    Xiaoxin Sheng