Forum Discussion
Running Total including blanks
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
Super UserIs 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].
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 ?
Here is a dropbox link to the sample file
- johnt75
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