Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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)
)
Solved! Go to 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
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
Thanks, but the code that I submit results in integers on dax.do, can you verify?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
66 | |
55 |