topic Re: Running Total Measure on 2 columns using CountA in DAX Commands and Tips

Running Total Measure on 2 columns using CountA

Pawluk — Tue, 09 Jul 2019 08:29:31 GMT

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_DATE	OLD_VALUE	NEW_VALUE
01/09/2018 00:00		New
03/09/2018 00:00		New
03/09/2018 00:00	New	Open
04/09/2018 00:00		New
04/09/2018 00:00		New
04/09/2018 00:00		New
04/09/2018 00:00	New	Open
04/09/2018 00:00	New	Open
05/09/2018 00:00		New
05/09/2018 00:00	New	Open
06/09/2018 00:00	New	Open

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

(The full Table has many more Statuses)

	New	Open
01/09/2018	1
03/09/2018	1	1
04/09/2018	2	3
05/09/2018	1	5

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?

Re: Running Total Measure on 2 columns using CountA

Anonymous — Tue, 09 Jul 2019 10:20:52 GMT

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

Re: Running Total Measure on 2 columns using CountA

Anonymous — Tue, 09 Jul 2019 11:07:33 GMT

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