Running count with changing categories

[Natsnebki]

Hi,

I try to get the following matrix out of this data:

Data:

Date	CAT	Status
22/01/01	a	YES
22/01/01	c	YES
22/01/03	b	YES
22/01/05	c	NO
22/01/08	c	YES
22/01/10	a	NO
22/01/10	b	NO

As you can see the status may change over time.

Wanted matrix:

Dates	YES	NO	Total
22/01/01	2	0	2
22/01/02	2	0	2
22/01/03	3	0	3
22/01/04	3	0	3
22/01/05	2	1	3
22/01/06	2	1	3
22/01/07	2	1	3
22/01/08	3	0	3
22/01/09	3	0	3
22/01/10	1	2	3

So the count of the status by day., also for the dates there is no data.

I used a related table (DATESINTABLE) with all dates between the MIN date and the Max date from the source, and with the folllowing measure I got this result:

Measure:

Running Dcount status by dimdate =

CALCULATE (

   DISTINCTCOUNT( 'Table'[CAT]),

    FILTER (

        ALLEXCEPT('Table', 'Table'[Status]),

        'Table'[Date]<= MAX(DATESINTABLE[Dates])

    )

)

MATRIX:

So I think i'm almost there, but maybe not. 

Any help would be much appreciated

Thank you

[Natsnebki · Accepted solution]

I solved it by creating a table with all dates between min date and max date, merge this full outer, sort by category and date and fill down all nulls in Status. 

[FreemanZ]

Hi @Natsnebki 

try to create a new table with the code below:

Table = 
VAR Table1 = 
	ADDCOLUMNS(
	    SUMMARIZE(
	        TableName, 
	        TableName[Date]
	    ),
	    "YES",
	    CALCULATE(
	        COUNTROWS(
	            FILTER(
	                TableName, 
	                TableName[Status] = "YES"
	            )
	        )    
	    ),
	    "NO",
	    CALCULATE(
	        COUNTROWS(
	            FILTER(
	                TableName, 
	                TableName[Status] = "No"
	            )
	        )    
	    )
	)
VAR Table2 =
	ADDCOLUMNS(
		Table1,
		"Total",
		[Yes]+[No]
		
	)
RETURN 
	Table2

i tried and it works like this:

your dataset:

[Jayee]

Hi @Natsnebki ,

Please refer to this Page for details about how to calculate running totals in DAX.

[daXtreme]

ALLEXCEPT does not work the way you think it does. Please refer to https://dax.guide/allexcept for details. 

Here's the code you'd most likely want:

// Base measure
# CATs = DISTINCTCOUNT( 'Table'[CAT] )

// Base measure - RT
# CATs RT =
var LastVisibleDate = MAX( Dates[Date] )
var Result =
    CALCULATE(
        // YOu add 0 to be able to see
        // also the rows which would
        // have BLANKs.
        [# CATs] + 0,
        Dates[Date] <= LastVisibleDate,
        REMOVEFILTERS( Dates )
    )
return
    Result

[Natsnebki]

Thank you for your suggestion. This measure adds 0's in my matrix instead of blanks, but the count is still the same, and not what i need. The fact that a cat has a changing status, is not counted correctly. I hope I'm clear