Count the Open Account Numbers per each category with latest date

[Naresh91]

Hi ,

i have the below Table

AccID	Category	AccStatus	Date
ab133	xyz	Open	1/1/2020
ab133	abc	Open	1/1/2020
ab133	cde	Open	1/1/2020
ab133	efz	Closed	1/1/2020
cd354	xyz	Open	1/6/2020
cd354	abc	Closed	1/2/2020
dc566	cde	Open	1/3/2020
gf345	xyz	Open	1/4/2020

i want to calculate "AccID" who have the "AccStatus" as "Open" for each "Category" with Latest Date.

it has to count the Highlighted Rows

AccID	Category	AccStatus	Date
ab133	xyz	Open	1/1/2020
ab133	abc	Open	1/1/2020
ab133	cde	Open	1/1/2020
ab133	xyz	Closed	1/2/2020
ab133	efz	Closed	1/1/2020
cd354	xyz	Open	1/2/2020
cd354	abc	Closed	1/6/2020
dc566	cde	Open	1/3/2020
gf345	xyz	Open	1/4/2020
gf345	xyz	Closed	1/1/2020

[amitchandak]

@Naresh91 ,

if this measure >0 , the you need to color the column as green using conditional formatting 

new Count =
VAR __id = MAX ('Table'[AccID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[AccID] = __id , 'Table'[AccStatus] = "Open")
CALCULATE ( Count ('Table'[Date] ), VALUES ('Table'[AccID] ),'Table'[AccID] = __id,'Table'[Date] = __date , 'Table'[AccStatus] = "Open" )+0

max open date 

new Date =
VAR __id = MAX ('Table'[AccID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[AccID] = __id , 'Table'[AccStatus] = "Open")
CALCULATE ( Max ('Table'[Date] ), VALUES ('Table'[AccID] ),'Table'[AccID] = __id,'Table'[Date] = __date , 'Table'[AccStatus] = "Open" )