Search code examples
powerbidaxformulapowerbi-desktop

Subtract a value from a previous row and repeat until 0

Screenshot of BI column needed

I need to create the highlighted yellow column like in this screenshot (Excel). But I need it in Power BI. The column starts with a known value (story points), subtracts the points per day value, which results in a number and then that "formula" needs repeated down until the answer is 0.

Actual Power BI image:
Power BI with Ideal Trend column

Solution

  • According to the screenshot from Power BI, the following DAX formula should create the calculated column which you need.

    I chose the columns which I think should work best, but you can rework the formula, as you know your data better.

    IdealTrend = 
var _sn = [Sprint Number]
var _min_date = minx(filter(tbl_ADO, [Sprint Number] = _sn), [Date])
return [Story Points] - [Story Points] / ([Sprint Length]-1) * ([Date]-_min_date)