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
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?
User | Count |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |