cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Naresh91
Microsoft
Microsoft

Count the Open Account Numbers per each category with latest date

Hi ,

 

i have the below Table

AccIDCategoryAccStatusDate
ab133xyzOpen1/1/2020
ab133abcOpen1/1/2020
ab133cdeOpen1/1/2020
ab133efzClosed1/1/2020
cd354xyzOpen1/6/2020
cd354abcClosed1/2/2020
dc566cdeOpen1/3/2020
gf345xyzOpen1/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

 
AccIDCategoryAccStatusDate
ab133xyzOpen1/1/2020
ab133abcOpen1/1/2020
ab133cdeOpen1/1/2020
ab133xyzClosed1/2/2020
ab133efzClosed1/1/2020
cd354xyzOpen1/2/2020
cd354abcClosed1/6/2020
dc566cdeOpen1/3/2020
gf345xyzOpen1/4/2020
gf345xyzClosed1/1/2020

 

 

1 REPLY 1
amitchandak
Super User IV
Super User IV

@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" )

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors