Forum Discussion

Natsnebki's avatar
Natsnebki
Frequent Visitor
3 years ago
Solved

Running count with changing categories

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...
  • Natsnebki's avatar
    Natsnebki
    3 years ago

    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's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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: