Year Difference Not getting correctly

[sunil-kumar5]

Hi,

I am using Age(StartDate,EndDate) function to get the years but the problem is this function count all the >=1 and <2 in a single year.

I have a start date like 04/05/2017 and end date as 01/05/2020 so this function giving me 2 years however I want 3 Years as a result from 2017 to 2020.

Please help!!

Thanks

[eliran]

If you want to rely on the month only, you can just use monthstart on the startdate, meaning it will always be the first of the month, thus giving you for your example a result of 3.

age('01/05/2020',MonthStart('04/05/2017'))

[tresesco]

If you need difference in year, use year(), like:

Year(EndDate) - Year(StartDate)

[sunil-kumar5]

Thanks for your response

I actually have two dates have data in millions, I just give the example of dates as mentioned above but want to looking for years. I Tried your formula but it is not giving me the right answers.

[sunil-kumar5]

Hi Tresesco,

When I used the same approach in power BI life Datediff([StartDate], [End Date],Year) I got the result like  for (Year -1)36000 and when I am trying your expression in qlik sense it gives the result of 55000 data.  Not sure which one is correct.

Please let me know your thoughts

Thanks

[Kushal_Chawda]

try below

 Age(yearstart(StartDate),yearstart(EndDate))