Count of weeks where a measure matches a value

[danielcarpenter]

Problem background:

• When an issue is resolved, it gets a resolution date
• By summing the issues on a per calendar week basis you get the weekly throughput of issues
• My model needs to calculate throughput dynamically as there are many issues with many teams and issues are not restricted from belonging to more than one team

What I’m trying to do:
I want to create a histogram of weekly throughput. In other words:

• For a filtered set of data…
  • How many weeks were 0 issues resolved?
  • How many weeks were 1 issue resolved?
  • How many weeks were 2 issues resolved?
  • ...

conceptually, this is the desired output...

What I’ve tried so far:

I have this measure that calculates resolved issues dynamically

resolved_count = 
	CALCULATE(
		DISTINCTCOUNT(issues[issue_key]),
		issues[status]="Done",
		issue_dates[date_type]="Resolved"
	)

Then I started trying to dynamically identify the count of weeks, BUT I can’t figure out how to filter this by if the resolved_count is = throughput_value. Maybe I need to go in a completely different direction. 

throughput_weeks_count =
    COUNTROWS(
         ADDCOLUMNS(
               VALUES(issue_dates_calendar[Calendar Week End Date]),
               "throughput",[resolved_count]
         )
    )

Normally I would use a filter function (logically, something like below), but I can’t figure out how to use that when I’m not referencing a real table.

FILTER(
      “throughput_weeks_count”,
      “throughput” = throughput_ranges[throughput_value]
       )

Any help is appreciated!

[danielcarpenter · Accepted solution]

I was able to resolve this by adding a "last day of week" calendar table to my data model then using this measure to calculate the count of weeks. Also added a min/max boundary to the throughput_range table.

VAR weeks = 
COUNTROWS(
    FILTER(
        issue_dates_calendar_weeks,
        AND(
            [resolved_count] >= MIN (throughput_ranges[value_min]),
            [resolved_count] <= MAX (throughput_ranges[value_max])
        )
    )
)

[bcdobbs]

Can you create a calculated table at load time basically with your syntax...

throughput_weeks_count =

         ADDCOLUMNS(
               VALUES(issue_dates_calendar[Calendar Week End Date]),
               "throughput",[resolved_count]
         )

(SUMMARIZECOLUMNS might be better)

That then materialises the count as a column which you can use as filter on your x axis.

[danielcarpenter]

No, can't do that even though that would be a lot easier. The weekly throughput values need to calculate dynamically because they are filterable by team_issue and issue attributes selections.

[bcdobbs]

Alternatively. Take a disconnected table with numbers 0 through 52 (or higher)

Put that on x axis.

Then in your last measure read the value with selectedvalue and wrap your count rows in a filtered version of the dynamic table. Can write the dax tomorrow if you want.

[danielcarpenter]

Maybe, not sure I completely follow.

How would that work for date periods that cross a year boundary or extend longer than a single year? Not sure how the weeks match up in that scenario.