Forum Discussion
Calculate excels DAYS360 / YEARFRAC DAX using Power Query M
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
Hi amrosullivan ,
You can create a custom column like this
(Number.From([EndDate]) - Number.From([StartDate]) ) /365Please 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! 😊
3 Replies
Hi amrosullivan ,
You can create a custom column like this
(Number.From([EndDate]) - Number.From([StartDate]) ) /365Please 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! 😊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/
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.
