Forum Discussion

Pawluk's avatar
Pawluk
New Member
7 years ago

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...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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