Open Count

[StoryofData]

Hi, 

Been lurking around the forum looking for an answer but I cannot figure it out.
I need to calculate # of open cases -  a backlog of cases that is carried over from previous day.

I have my main query 'Query 1' and a calendar table - 'Time Period Daily'

Here is what I have so far:

Cases Opened: 

CALCULATE(

DISTINCTCOUNT(Query1[CaseNumber]),

USERELATIONSHIP('Time Period Daily'[Full Date],Query1[DateOpened])

Cases Closed:

CALCULATE(

DISTINCTCOUNT(Query1[CaseNumber]),

USERELATIONSHIP('Time Period Daily'[Full Date],Query1[DateClosed])

Cases Still Open:

CALCULATE(DISTINCTCOUNT('Query1'[CaseNumber]),

FILTER('Query1', ('Query1'[DateOpened] <= Calculate(MAX('Time Period Daily'[Full Date]))

&& 'Query1'[DateClosed] > Calculate(MIN('Time Period Daily'[Full Date]))  ||  'Query1'[DateOpened] <= Calculate(MAX('Time Period Daily'[Full Date]))

&& ISBLANK('Query1'[DateClosed]))))

Here is my result, however, it seems to calcute August wrong - if one of the cases was closed on August 22nd then it should be 3 not 4 cases. 

When I drill down to date level, data shows accurately.

And, how do I ensure that "Cases Still Open" measure shows only up to current date not my entire 'Time Period Daily' table?

[v-rzhou-msft · Accepted solution]

Hi @StoryofData ,

I update your measure and get the correct result.

Cases Still Open = 
CALCULATE (
    DISTINCTCOUNT ( 'Query1'[CaseNumber] ),
    FILTER (
        'Query1',
        (
            'Query1'[DateOpened] <= MAX ( 'Time Period Daily'[Full Date] ) 
                && 'Query1'[DateClosed] > CALCULATE ( MAX ( 'Time Period Daily'[Full Date] ) )
                || 'Query1'[DateOpened] <= CALCULATE ( MAX ( 'Time Period Daily'[Full Date] ) )
                    && ISBLANK ( 'Query1'[DateClosed] )
        )
    )
)

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@StoryofData , refer this approch

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

and file attached