Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
reinaldoc_
New Member

YEARFRAC bug?

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

 

 

image.png

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

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

OwenAuger_0-1699768673979.png

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.