Running Total including blanks

[llewilliams95]

Hi,

I have an issue where i'm trying to calculate a Running Total but if i have a blank value for an attribute then the running total is ignored. Please see attached screenshot of my table visual, I have visual level filters applied to this table.

The running total seems to be working fine until it reaches 36, the visual filters means there are no rows for this but i would expect the running total to continue by adding 0 as opposed to applying 679, the value to anything less than 0 Closed deal Time.

Here is the expression i'm using to calculate the Running Total 

Running Total =

CALCULATE([SnapshotDealRowCount],

            FILTER(ALL('SnapshotDeal'[ClosedDealOpenTime]),

             'SnapshotDeal'[ClosedDealOpenTime] <= MAX('SnapshotDeal'[ClosedDealOpenTime])

            )

)

[SnapshotDealRowCount] is a simple COUNTROWS measure - COUNTROWS(SnapshotDeal)

Is it possible to continue the running total when there are blanks?  Any help would be greatly appreciated.

[johnt75]

Is the column in the visual 'SnapshotDeal'[ClosedDealOpenTime] or is it a different column, maybe from a dimension table ? If it is a different column then use that column in the MAX function rather than 'SnapshotDeal'[ClosedDealOpenTime].

[llewilliams95]

The column in the visual is the same as 'SnapshotDeal'[ClosedDealOpenTime] used in the expression. 

I have created a separate table containing all values from the ClosedDealOpenTime column and used the field from the new table in the MAX function but nothing changed. The Running Total still doesn't carry through the blank values.

[johnt75]

I don't quite understand how the value 36 is appearing if no rows correspond to that number. Can you share a sample PBIX file ?

[llewilliams95]

Here is a dropbox link to the sample file

https://www.dropbox.com/s/ikj8ly93pazz0q3/Sample.pbix?dl=0 

[johnt75 · Accepted solution]

The problem is that the MAX is returning a blank value when that time doesn't exist in the filtered data set. You can use

Running Total =
VAR ReferenceTime =
	CALCULATE(
		MAX( 'SnapshotDeal'[ClosedDealOpenTime] ),
		ALLEXCEPT(
			'SnapshotDeal',
			'SnapshotDeal'[ClosedDealOpenTime]
		)
	)
VAR Result =
	CALCULATE(
		[SnapshotDealRowCount2],
		'SnapshotDeal'[ClosedDealOpenTime] <= ReferenceTime
	)
RETURN
	Result