need help with total calculation

[Jeanxyz]

I encounter difficulty with total calculation. I have a table where each Jira issue is assigned to a Team and a Sprint. Each sprint has a start date and end date. If an issue is solved before the sprint end date, the issue is considered completed on time. I need to add up the story points of all the issues solved on time. Below is the Dax measure I created:

For some reason the total line of story_points_completed(M) is not correct. I should have 407 points completed (i.e., sum of each spint line), but Power BI gives me 712.5 instead. 

Thanks for help!

*******************

story_points_completed(M) =

var amt=

Sumx(

values(dim_IssueSprints),

Calculate(sum(IssueSprints[story_points]),IssueSprints[Issue_Status_Cat]="Done",IssueSprints[Status_Change_Date]>=min(IssueSprints[Start_Date]),

IssueSprints[Status_Change_Date]<=max(IssueSprints[End_Date]))

)

return

if(amt=0,blank(),amt)

***************************

@amitchandak , @parry2k 

[Jeanxyz · Accepted solution]

I finally got it right! Here is the correct formula and toal amount matches now. The previous measure didn't work because I thought by using values() , I created a virtual table which  is automatically linked to the fact table for filtering purpose. There is no relationship between the virtual table and fact table, so I need to quote the filter context explicitly in my filter condition. 

story_points_completed(M) =

var amt=

Sumx(

values(dim_IssueSprints),

Calculate(sum(IssueSprints[story_points]), filter(issuesprints,IssueSprints[Status_Change_Date]>=dim_IssueSprints[Start_Date] &&

IssueSprints[Status_Change_Date]<=dim_IssueSprints[End_Date] && IssueSprints[Sprint_Issue]=Dim_IssueSprints[Sprint_Issue] && IssueSprints[Issue_Status_Cat]="Done"))

)

return

if(amt=0,blank(),amt)

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Jeanxyz]

@Ashish_Mathur 

Here is the sample file. Thanks!

https://www.dropbox.com/s/eirkm0n3wsokcz7/Jira%20Demo2.pbix?dl=0

[FreemanZ]

hi @Jeanxyz 

try like:

story_points_completed(M) =

var amt=

Calculate(

    sum(IssueSprints[story_points]),

    IssueSprints[Issue_Status_Cat]="Done",

    IssueSprints[Status_Change_Date]

         >=min(IssueSprints[Start_Date]),

    IssueSprints[Status_Change_Date]

         <=max(IssueSprints[End_Date])

)

return

if(amt=0,blank(),amt)

[Jeanxyz]

This won't work because each sprint has its unique start date and end date. This needs to be taken into consideration.