YEARFRAC
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
YEARFRAC is a built-in DAX function that calculates the fraction of a year between two dates. This function is commonly used in financial and accounting reports, where it is necessary to calculate the number of years between two dates. In this article, we will explore how to use the YEARFRAC function in Power BI.
Syntax of the YEARFRAC Function
The syntax of the YEARFRAC function is as follows:
YEARFRAC(start_date, end_date, [basis])
- start_date: This is the date from which the calculation will begin.
- end_date: This is the date at which the calculation will end.
- basis: This argument is optional. It specifies the day count basis to be used in the calculation. The default value is zero, which indicates a US (NASD) 30/360 basis.
Using the YEARFRAC Function in Power BI
To use the YEARFRAC function in Power BI, follow these steps:
1. Open your Power BI report and select the table or column that contains the dates you want to calculate the fraction of a year between.
2. Click on the "New Measure" button in the "Modeling" tab.
3. In the formula bar, type the following formula:
Fraction of Year = YEARFRAC(Table[start_date], Table[end_date])
Replace "Table" with the name of the table that contains the dates, and "start_date" and "end_date" with the names of the columns that contain the start and end dates, respectively.
4. Press Enter, and the measure will be created.
5. You can now add the measure to your report and use it in your visualizations.
Example
Suppose you have a table named "Sales" that contains the following columns:
- Date: The date of the sale.
- ID: The ID of the sale.
- Total: The total amount of the sale.
To calculate the fraction of a year between the date of each sale and the end of the year, follow these steps:
1. Click on the "New Measure" button in the "Modeling" tab.
2. In the formula bar, type the following formula:
Fraction of Year = YEARFRAC(Sales[Date], DATE(YEAR(MAX(Sales[Date])), 12, 31))
This formula calculates the fraction of a year between the date of each sale and the end of the year.
3. Press Enter, and the measure will be created.
4. You can now add the measure to your report and use it in your visualizations.
The YEARFRAC function is a useful DAX function in Power BI that can be used to calculate the fraction of a year between two dates. This function is commonly used in financial and accounting reports, and it is easy to use in Power BI. By following the steps outlined in this article, you can start using the YEARFRAC function in your own Power BI reports.
