Calculate open cases prior to set date

[HChoice]

Hi friends,

I appreciate this is a repeated topic, but can't get any of the suggested answers I came across to work.

I'm trying to calculate the number of cases that were open at the start of a time period. The filter I have on my report is by quarter. I need to use DISTINCTCOUNT as the identifier is duplicated in my table(Matters[matter_id]), and I'm only looking to return the number of Representation services (Matters[Matter Type]).

I've been able to get the correct value with:

Rep Open at Start = CALCULATE(DISTINCTCOUNT(Matters[matter_id]),

FILTER(ALL(Matters), Matters[Matter Type] = "Representation Services"),
FILTER(ALL(Matters), Matters[Date Created] < MIN('Calendar'[Date])),
FILTER(ALL(Matters), OR(Matters[date_closed] >= MIN('Calendar'[Date]), Matters[date_closed] = BLANK())))

However, I need to be able to introduce categories with this measure. At the moment, it just repeats the same number (as you'd expect when using ALL). If I try and use ALLEXCEPT or REMOVEFILTERS, it gives me a blank column (which assume means it hasn't completely removed the Calendar[Date] filter).

This is what my model looks like. I'm trying to introduce a location filter from Location by Postcode[State]

I though it might be an issue with my model, but even if use a category from Participants, I have the same problems.

[HChoice]

@amitchandak  This is fantastic, right up until I need to include the Matter Type filter.

If I don't include the Matter Filter, it works perfectly, but the filter messes with it and Cumm Hire and Cumm Termination return blank values. (Unfortunately I need the filter in the measure.)

This is the measures I created based on your suggestions. (I used your measure names here for convenience.)

Terminated = CALCULATE(DISTINCTCOUNT(Matters[matter_id]),

USERELATIONSHIP(Matters[date_closed],'Calendar'[Date]),

NOT(ISBLANK(Matters[date_closed])))

Cumm Hire = CALCULATE(DISTINCTCOUNT(Matters[matter_id]),

FILTER(Matters, Matters[Matter Type] = "Representation Services"),

FILTER(ALL('Calendar'), 'Calendar'[Date] < MIN('Calendar'[Date])))

Cumm Termination = CALCULATE([terminated],

FILTER(Matters, Matters[Matter Type] = "Representation Services"),

FILTER(ALL('Calendar'), 'Calendar'[Date] < MIN('Calendar'[Date])))

Current Employee = [Cumm Hire] - [Cumm Termination]

I've double checked the spelling of the Matter Type filter and actually copied and pasted it from the data set to be sure.

Any suggestions?

[amitchandak]

@HChoice , I have these for HR, where Start date is joined, You need very similar code

Cumm Hire = CALCULATE([Hire], FILTER(ALL('Date') , 'Date'[Date] <= max('Date'[Date])))
Cumm Termination = CALCULATE([Teminated], FILTER(ALL('Date'), 'Date'[Date] <= Max('Date'[Date])))
Current Employee = [Cumm Hire] -[Cumm Termination]  //Open in your case

Other approach

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

You might like to use < in place <=