Last week I discovered a really weird issue in Power BI desktop. I have written a simple DAX measure to calculate a CAGR using linear regression fit. It consists of several components (VAR sumx, sumxx, sumy and sumxy). The code worked excellent on a column with units (type: whole number), but I failed to get it working on another data source with Revenues (type: $). All x data (years, type: whole number) and y data are positive.
To my surprise, sometimes sumxy was showing negative numbers, leading to completely wrong results.
After debugging for a long time, I didn't find anything wrong with the code and decided out of desperation to change the format of the Revenue column from $ to whole number in the power query editor. Since then the code works perfectly fine.
I'm completely puzzled by the root cause of the issue, but would like to save other Power BI users from this issue.
Any ideas what went wrong here?