YEARFRAC bug?

Hi reinaldoc_ 

I believe this is the intended behaviour of YEARFRAC, and you will get the same result using Excel's YEARFRAC function.

 

Did you expect integers to be returned in all cases? You could use Basis argument = 0 as one option.

 

With the Basis argument set to 1, the calculation is

 

YEARFRAC = (EndDate - StartDate) / ( Simple average of days per complete calendar year for the years spanning StartDate to EndDate-1 )

 

For 2020, the calculation would be

YEARFRAC = ( DATE(2023,11,11) - DATE(2020,11,11) ) / ( (366+365+365+365)/4 ) = 2.998

 

You can verify this by adding a manual calculation to the query like this:

https://dax.do/x3vEBqrkCHEjkR/

 

EVALUATE
VAR _T =
    ADDCOLUMNS (
        GENERATESERIES ( 2010, 2022, 1 ),
        "Today in the past", DATE ( [Value], MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
RETURN
    ADDCOLUMNS (
        _T,
        "YEARFRAC", YEARFRAC ( [Today in the past], TODAY (), 1 ),
        "YEARFRAC Manual",
            VAR DateRange =
                ADDCOLUMNS (
                    CALENDAR ( [Today in the past], TODAY () - 1 ),
                    "@Year", YEAR ( [Date] )
                )
            VAR NumDays =
                TODAY () - [Today in the past]
            VAR DaysPerYear =
                ADDCOLUMNS (
                    GROUPBY ( DateRange, [@Year] ),
                    "@DaysPerYear",
                        (
                            DATE ( [@Year] + 1, 1, 1 )
                                - DATE ( [@Year], 1, 1 )
                        )
                )
            VAR DaysPerYearSimpleAverage =
                AVERAGEX ( DaysPerYear, [@DaysPerYear] )
            RETURN
                DIVIDE ( NumDays, DaysPerYearSimpleAverage )
    )

Regards

That appears to be deceptive formatting. Turn off Auto format to see the values: