How to have a running balance of distinct counts based on different column dates

[GMC]

Hi, I have a transaction table that has a start date and completion date columns. I need to determine the count of OPEN transactions per month. A transaction is considered open for a reference month if its start date is on or before that month, AND ( Completion date is either blank, OR its month is after the reference month). I have to have it in such a way, so when I plot its graph, report filters using the transaction table columns are still applicable.   

For example: the first table shows the data set, and the second table shows the expected Open ItemRunning Balance.

The challenge I have is to come up with the correct measure so that it is report filterable on the fly. 

Thank you in advance.

[Anonymous · Accepted solution]

// For this you need a DateTable that's DISconnected from Documents.
// You want this measure to work correcly not only for months
// but for any period of time, even 1 day. Generally, you should
// always aim at creating measures that make sense for ANY
// selection in dimensions. I'd also suggest that you not use
// BLANKs for Completion Date. Instead, use something like
// DATE(3000, 3, 1). This will make the code sipler and potentially
// faster as you won't need to check for BLANK dates. Of course,
// the Start Date and Completion Date columns should be hidden.

[# Open Docs] =
var __lastVisibleDate = MAX( DateTable[Date] )
var __result =
	CALCULATE(
		COUNTROWS( Documents ),
		// assuming that Start Date will never be BLANK...
		Documents[Start Date] <= __lastVisibleDate,
		OR(
			Documents[Completion Date] > __lastVisibleDate,
			// If you remove BLANKS as suggested, you can
			// remove OR and leave only the first condition
			// under OR.
			ISBLANK( Documents[Completion Date] )
		)
	)
RETURN
	__result

[GMC]

Thanks Daxer! I have accepted it as the solution.

[lbendlin]

use a CROSSFILTER(...,NONE)  to fill in the date gaps.  That will then allow your measure to mark each transactions status by date.

[GMC]

Hi Ibendlin, please elaborate on the application of crossfilter. Thank you