values() filter doesn't pass to the table

[Jeanxyz]

I have been fighting with a Dax formula for hours. I wonder if someone can help. 

I have an IssueSprint table as below. Each Jira issue is assigned to a sprint (10 work days). Each Sprint has a Start_Date and End_Date, if an issue is not solved during the period, it's passed onto next sprint. Some issues receive story points, which is an workload indicator. 

I have 58 story points in Demo Sprint 17, and I expect in each work day 10% issues are solved, so the remaining story points by end of day 1 is 90%*58, by day2, 80%*58 ....

I try to create a measure (guideline)  to duplicate this calculation logic, but it doesn't work. sample file: https://www.dropbox.com/s/0rxpfy4gfnxcucm/Jira%20Demo.pbix?dl=0

guideline(M) =

Var select_date=min(max('Calendar'[Date]),today())

var amt=

sumx(

values(IssueSprints),

calculate(max(IssueSprints[story_points])*

(1- Divide(

if(select_date<calculate(max(IssueSprints[Start_Date])),1,

//if select date<start date, completion rate=1,

if (select_date>calculate(max(IssueSprints[End_Date])),1,

//if select date>end date, completion rate=1,

calculate(sum(Calendar[Workday_BE]),Calendar[Date]<=select_date,Calendar[Date]>=calculate(max(IssueSprints[Start_Date])))

//select date falls betweeen start_date & end_date, calculate no of workdays from start_date to select_date

)),

10))))

return

amt

@amitchandak , @parry2k , @otravers, @Greg_Deckler 

[v-shex-msft]

HI @Jeanxyz,

I'm glad to hear you find the reason and sharing the formula here, I think they may help other who faced the similar issue.

Regards,

Xiaoxin Sheng

[Jeanxyz · Accepted solution]

I think I figured it out. 

There was a bug in the divide () part, also I created a dim_issuesprints table for use in the values(). 

guideline =

Var select_date=min(max('Calendar'[Date]),today())

var amt=

sumx(

values(dim_IssueSprints),

calculate(max(IssueSprints[story_points])* (1-

calculate(

divide(

if(select_date<max(IssueSprints[Start_Date]),10,

if (select_date>max(IssueSprints[End_Date]),10,

calculate(sum(Calendar[Workday_BE]),Calendar[Date]<=select_date,Calendar[Date]>=max(IssueSprints[Start_Date])))),

10)))))

var start_date=minx(values(dim_issuesprints),calculate(max(IssueSprints[Start_Date])))

return

amt