Forum Discussion

StoryofData's avatar
StoryofData
Helper III
2 years ago
Solved

Open Count

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?

 

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies