Calculate excels DAYS360 / YEARFRAC DAX using Power Query M

[amrosullivan]

Hello all,

i need to calculate days360 or YEARFRAC between two dates in power query. how do i do this?

this is what i need to replicate but in M

 YEARFRAC([Start Date],[End Date],0)

i need to create a custom column

thanks,

Amrita

[Anonymous]

Hi @amrosullivan ,

The Query Editor side of things uses a language called Power Query M, not DAX. As far as I know, YEARFRAC is only available in DAX, so that would be a measure or a calculated column after your data is loaded, rather than a custom column in the Query Editor.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

This will have to be done through a proper function as it can't be done through one liner formulas. Too many conditions to be handled regarding leap year and start and end dates. Here is M version for YEARFRAC

https://www.thebiccountant.com/2022/03/12/excel-yearfrac-for-power-query/ 

[rohit_singh · Accepted solution]

Hi @amrosullivan ,

You can create a custom column like this 
(Number.From([EndDate]) - Number.From([StartDate]) ) /365

Please keep in mind this does not take into account leap years.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊