YEARFRAC

YEARFRAC

How to Use the Power BI DAX function YEARFRAC

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.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: