If you want to solve Excels XIRR-function with M in PowerBI or PowerQuery you have to use a goal seeking algorithm. I tried it with a binary-search and the results were quite good (on my scale):
Wondering if there are other solutions out there or different techniques regarding the “helper”-elements I had to include here, so please come forward 🙂
The goal-value is formulated in a way that it should be zero, as this is what the binary-search procedure is aiming at. In this case it’s the XNPV. Other cases could be Break-Even for example, where the accumulated sales match the accumulated costs. In that case you would write: Result = sales – costs .
Subscribers can check it out in this file: GoalSeekForXIRR.xlsx
Enjoy & stay queryious 🙂
I’ve learned my lesson with Kmeans clustering algorithm and since stopped trying to implement number crunching algos in M 🙂
Nevertheless the script you have here is a great example of how to do (for/while) loops in M.
By the way, have you seen how the clustering was implemented in the tabular model? Apparently there is a KMeansClustering function in DAX. I guess this is where most of the algos will be implemented eventually.
Hi Igor, thx!
Have you shared your lessons learned re the Kmeans yet? (Looks like I’ve missed that)
Haven’t seen KMeans Clustering in DAX either. Can you please share a link?
Thx and cheers, Imke
Pingback: #Excel Super Links #50 – shared by David Hager | Excel For You
Pingback: Writing data to GitHub using Power Query only – The BIccountant