How to get Month wise open Count , Close Count and net count

[Anonymous]

Source and Expected Output

[ryan_mayu · Accepted solution]

@Anonymous 

here is a workaround for you

create a closecount column

closecount = if(ISBLANK('Table'[closed_date]),0,if('Table'[closed_date]=MAXX(FILTER('Table','Table'[Incident_Number]=EARLIER('Table'[Incident_Number])),'Table'[closed_date]),1,0))

then create measures

Open count = DISTINCTCOUNT('Table'[Incident_Number])

Close count = 
CALCULATE(SUM('Table'[closecount]),USERELATIONSHIP('Calendar'[Date],'Table'[closed_date]))

opennet = [Open count]-[Close count]

However , I have a question.

1244306, last close in Nov

1250824 , last close in Dec

1050761, last close in Oct.

So should be 1 for Oct, Nov and Dec. How come your result is 2,0,1?

please see the attachment below

[Anonymous]

Hi @ryan_mayu  is there any way to create below column in Measure..? 

Closecount = if(ISBLANK('Table'[closed_date]),0,if('Table'[closed_date]=MAXX(FILTER('Table','Table'[Incident_Number]=EARLIER('Table'[Incident_Number])),'Table'[closed_date]),1,0))

[Anonymous]

@ryan_mayu  Thanks for spending your time on this task.

Yes you're right it should be 1 for Oct. I made mistake on output calculation.

Once agian thank you very much @ryan_mayu .. It's very useful to me. 

[Anonymous]

For the above post here is the conditions to be maintained. Can some one help me to achive.

Open Count: For a given month, total distinct count that had at least 1 CA that was open as on EOM

Close Count: For a given month, total distinct count for which the last CA was closed in that month