YEARFRAC bug?

[reinaldoc_]

Its normal or is a bug with YEARFRAC?

Table = 
  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)
    )

[OwenAuger · Accepted solution]

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

[OwenAuger · Accepted solution]

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

[OwenAuger · Accepted solution]

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

[reinaldoc_]

Thanks, but the code that I submit results in integers on dax.do, can you verify?