Forum Discussion
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...
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
Here is a dropbox link to the sample file
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