Subtract a value from a previous row and repeat until 0

1
votes

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

1
powerbidaxformulapowerbi-desktop
Hello! D2 would equal the value in the total points column (not summarized, just the value so in this case, 34). Then the subsequent formula for the cells below can be seen in the formula bar. - Josh Lambert

1 Answers

0
votes

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)