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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JoelN
Helper I
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
FarhanAhmed
Community Champion
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!




View solution in original post

@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!

View solution in original post

2 REPLIES 2
FarhanAhmed
Community Champion
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!




@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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors