Average difference date value is different in power BI

[Dunner2020]

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?

[Anonymous]

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

[Ashish_Mathur]

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.

[mahoneypat]

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]

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