Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Running Total including blanks

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...
Need Help
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    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
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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 ?

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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