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

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
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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