Forum Discussion
Running Total Measure on 2 columns using CountA
I am trying to create a Running Total using COUNTA on two columns to give a running count of statuses at the end of each day. If a Value is in the NEW_VALUE I want to Add 1 to the running total for t...
And I know exactly why you get 0 :) The empty cells in your table are not really empty. They do hold strings; they might be of zero length but they're still strings. Please replace them with proper BLANK()'s.
Best
Darek
Mate, here's the running total and it does what you wanted. If you want to know what the constituent parts return (the parts with a double underscore __), then just replace the output with the names of the variables.
Running Total =
var __visibleDate = SELECTEDVALUE( Dates[Date] )
var __dateExistsInData =
NOT ISEMPTY(
FILTER(
All( Data[CHANGE_DATE] ),
Data[CHANGE_DATE] = __visibleDate
)
)
var __countOld =
CALCULATE(
COUNTA( Data[OLD_VALUE] ),
Data[OLD_VALUE] <> BLANK(),
Dates[Date] <= __visibleDate,
ALL( Data )
)
var __countNew =
CALCULATE(
COUNTA( Data[NEW_VALUE] ),
Data[NEW_VALUE] <> BLANK(),
Dates[Date] <= __visibleDate,
ALL( Data )
)
var __total = __countNew - __countOld
return
if( __dateExistsInData, __countNew - __countOld )Please note that I've created a proper Dates table and marked it as such, then joined it to the date column in the Data table. When you put your data on a visual, do not use the CHANGE_DATE (this field should be hidden). Use the Date field from the Dates table, like so:
Best
Darek