Stack chart limit item by max date across column sections

[Anonymous]

I have issues that have different statuses across time and are assigned story points. Time intervals could be day, week, month and are the x-axis. My requirement is an issue's story points can only show up once in a column. An issue could transition between OPEN to IN PROGRESS in one time interval but only the story points  from the state with the lastest date is used. 

The problem I am having is the IN PROGRESS total in my second week has additional story points that should not be there. The other columns are correct.

This is my excel data: Yellow highlighted should be the only rows added to stack chart.

I created a table and a stack chart using the above data and a standard date_dim. The week, month and year data items were merged into the final table. In this example, I'm only looking at a week interval - dim.week_nbr. I highlighted below the row in yellow being added into the second week incorrectly. I expected to see only the row circled in black in the second week. It is the most recent dated status for issue 10 in week 2. All seems to look correct in the table in the measure columns. The story points are blank for rows I am not interested in.

last_date_m = CALCULATE (MAX ('data'[end_date] ), ALLEXCEPT (data,data[issue_id],data[dim.week_nbr]))

story_points_wk_m = SUMX(FILTER(data, MAX (data[end_date] ) = [last_date_m]),[story_points])

story_points_week_m = SUMX(FILTER(data, MAX (data[end_date] ) = CALCULATE (MAX ('data'[end_date] ), ALLEXCEPT (data, data[dim.week_nbr],data[issue_id]))),[story_points]). In this measure I combined the first 2 measures to see if could. Seems to work.

Table: 

Stack Chart:

Any ideas on why this is happening in the second week?

[lbendlin]

calculate a DAX table with issue_id, status, story_points and week and then make it DISTINCT(). That gets rid of the duplicate IN PROGRESS in week 2

[Anonymous]

Ibendlin, Thanks for taking the time to look at this. Must appreciated! 

I'm new to DAX. The code I wrote above was a big stretch for me. Would you mind giving me a little more? What do you mean by "calculate a DAX table"? Do you mean use a CALCULATETABLE? 

Thanks!

[lbendlin]

There are always many ways to do things in DAX .  CALCULATETABLE, ADDCOLUMNS, SELECTCOLUMS, SUMMARIZE, GROUPBY - choose your weapon as needed.

[Anonymous · Accepted solution]

Ibendlin, Again thanks.

You made me think and I was able to create a solution!! I will post tomorrow (on vacation today!). I created a calculated column and filtered on rows where the  story point value was not blank. I made a seperate column for day, week, month and was able to create seperate pages for a stack chart on each interval using the "data" table. 

Steven