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 that value. If it is in the OLD_VALUE column I want to Subtract 1.

 

CHANGE_DATEOLD_VALUENEW_VALUE
01/09/2018 00:00 New
03/09/2018 00:00 New
03/09/2018 00:00NewOpen
04/09/2018 00:00 New
04/09/2018 00:00 

New

04/09/2018 00:00 New
04/09/2018 00:00NewOpen
04/09/2018 00:00NewOpen
05/09/2018 00:00 New
05/09/2018 00:00NewOpen
06/09/2018 00:00NewOpen

 

From the table above (Taking "New" and "Open" as an example) I would expect to see:

(The full Table has many more Statuses)

 

 NewOpen
01/09/20181 
03/09/201811
04/09/201823
05/09/201815

 

Using the Measure:

 

RunningTotal =
CALCULATE(
    COUNTA('Table'[NEW_VALUE]) - COUNTA('Table'[OLD_VALUE]),
    FILTER(
        ALLSELECTED('Table'[CHANGE_DATE]),
        ISONORAFTER('Table'[CHANGE_DATE], MAX('Table'[CHANGE_DATE]), DESC)
    )
)
 
The counts for each column always end up a ZERO.
 
Does anyone know what I am doing wrong?

2 Replies

  • 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