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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Paginated Report - YEARFRAC similar function

Hey Guys!

Im trying to accomplish the same fuction of YEARFRAC like excel has but in my paginated report. I tried DATEDIFF expression and got close by not the correct answer. Any help is much appreciated.

Goal: (YEARFRAC Date1, Date2) Ex. YEARFRAC("8/28/2020","12/31/2021")

Answer: 1.34

Attempt:
=CStr(DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate"))\12)&"."& CStr(DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate")) Mod 12)
Attempt Answer: 1.4

Thanks for your help!

2 ACCEPTED SOLUTIONS
Community Champion

Try

``= DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate")) / 365``

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!

Helper I

@FarhanAhmed the solution works! I had to adjust the 365 to 12.
Do you know how to two decimals instead on showing just one?
Thanks!

2 REPLIES 2
Community Champion

Try

``= DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate")) / 365``

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!

Helper I

@FarhanAhmed the solution works! I had to adjust the 365 to 12.
Do you know how to two decimals instead on showing just one?
Thanks!

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors