I gave this a whirl, it didn't seem to work properly with the data, It created two trend lines (one for each state[color]). Thanks for the attempt on it.
I've come up with the solution for this. It involves a few calculated fields.
Create a "Date" calculated field that timeboxes the historical data within the confines of your sprint (this is required for creating the charts so that the x-axis fits between the sprint start and end day.
Date = IF [DateSK]<=[Microsoft_VSTS_Scheduling_StartDate]Then [Microsoft_VSTS_Scheduling_StartDate] ElseIF [DateSK] > [Microsoft_VSTS_Scheduling_FinishDate] Then NULL Else[DateSK] End
Then create these fields to produce the trend line:
First = if index()=1 then sum([Microsoft_VSTS_Scheduling_RemainingWork]) else NULL end
Last = lookup( sum([Microsoft_VSTS_Scheduling_RemainingWork]),last() )
window_max_date = window_max( max([Date]))
if index()=1 then sum([Microsoft_VSTS_Scheduling_RemainingWork])
elseif attr([Date])= window_max( max([Date])) then 0
And your chart should look something like figure 1 below. Unfortunately, this still doesn't solve my problem completely. If the data for sprint is partially completed, ie., the sprint is in progess, the chart x-axis shortens as there isn't data for the remaining portion of the sprint yet. As a result, this trend line shortens to match the existing data, producing an inaccurate ideal trend. Ideas?
I am trying to do exactly the same as you were here. Would you be able to share a packaged workbook of yours to help me get started?