cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ChrisN Frequent Visitor
Frequent Visitor

Count open cases and those closed within the last calendar month

I've created a database to track cases.
My report user needs to be able to get a count of cases that remain open and those that were closed within the last calendar month.  Each case has an open date and a close date, which is blank until it is closed.  She needs to be able to go back and see what the count was on the last day of the given month. 

I've used slicers and get pretty close by simply counting the "blank" close dates, but I'm stuck on figuring out how to get the cases that were closed within the calendar month.  I've tried creating measures but nothing has worked so far.

I would appreciate any help / insights you might have.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisN Frequent Visitor
Frequent Visitor

Re: Count open cases and those closed within the last calendar month

It actually was easier for me to write a report into the the database.   Here's the SQL statement:

SELECT COUNT(*) as casecount,site.site_name FROM cases INNER JOIN site ON (case_site = site_ID) WHERE ((cases.case_open <= '2019-03-31') AND ((cases.case_close IS NULL) OR ((cases.case_close >= '2019-03-01') AND (cases.case_close <= '2019-03-31')))) GROUP BY cases.case_site ORDER BY cases.case_site

which gives the results I was looking for:
IPMsample2.JPG

I just added a date picker to the page and it generates the report for whatever Month / Year selected.

I would still be interested to know if it's possible to do in Power BI.

4 REPLIES 4
Community Support Team
Community Support Team

Re: Count open cases and those closed within the last calendar month

@ChrisN ,

 


@ChrisN wrote:

I've created a database to track cases.
My report user needs to be able to get a count of cases that remain open and those that were closed within the last calendar month.  Each case has an open date and a close date, which is blank until it is closed.  She needs to be able to go back and see what the count was on the last day of the given month. 

I've used slicers and get pretty close by simply counting the "blank" close dates, but I'm stuck on figuring out how to get the cases that were closed within the calendar month.  I've tried creating measures but nothing has worked so far.

I would appreciate any help / insights you might have.


Could you share more details about the "last calendar date"? If possible, could you share some sample data for further analysis?

 

Community Support Team _ Jimmy Tao

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

ChrisN Frequent Visitor
Frequent Visitor

Re: Count open cases and those closed within the last calendar month

@v-yuta-msft ,thanks for the response.
Below is a sample of my data. As you can see, all cases have an opening date, some have a closing date and some are blank, indicating that they are still open.  I want to be able to see all of the cases that are still open AND all of the cases that WERE open on March 1st but closed in March 2019 (or Feb. 2019, or whatever month).
Mathematically, it's simple - the sum of open cases PLUS the number of cases closed in the calendar month.
I've tried creating a measure for this, but not getting it right. 

I would like for it to be dynamic - able to change which month we're looking at.  I have used a slicer on the "case_close" column and can set the date to "before" the last day  of the month, but I seem to be missing a few.

 

IPMsample.JPG

Greenwoodr Regular Visitor
Regular Visitor

Re: Count open cases and those closed within the last calendar month

I had a similar issue which I solved as follows :

1) Add the startdate and enddate to the fact table (I struggled to get it working using date ID's rather than the actual date - probably because this wasn't my active relationship between the fact and date dimensions and I had trouble alternating the active relationship twice in one measure) 

2) I had a  flag,opened (1 = yes , 0=No), representing any line in the fact table which was opened at any point. The primary purpose was to count number of new opened cases by start date but was useful here.

2) Ensure that the date dimension has an additional column representing the Current month end date relating to that date

 

Then using the following code I can sucessfully get the number of active cases (Ones where the end date is missing, as they are open, or in the future) at the end of each month.

Active Count = 
   calculate 
   (
        sum(Opened),
        filter(all('Fact '),
       'Fact'[Startdate]<=max('Date Dimension'[Current_month_end_Date])
       && 
       (
              'Fact'[ENDDATE]>max('Date Dimension'[Current_month_end_Date]) 
               ||
               'Fact'[ENDDATE]=blank())
      )
)

Hope that helps

Richard

 

PS. I would now like to add the results together , I.e calculate an average for the number active cases at the end of each month over the last 12 months , but am struggling to work out how to do this as these values are only available when calculated in the correct context and not actually stored. Any ideas?

ChrisN Frequent Visitor
Frequent Visitor

Re: Count open cases and those closed within the last calendar month

It actually was easier for me to write a report into the the database.   Here's the SQL statement:

SELECT COUNT(*) as casecount,site.site_name FROM cases INNER JOIN site ON (case_site = site_ID) WHERE ((cases.case_open <= '2019-03-31') AND ((cases.case_close IS NULL) OR ((cases.case_close >= '2019-03-01') AND (cases.case_close <= '2019-03-31')))) GROUP BY cases.case_site ORDER BY cases.case_site

which gives the results I was looking for:
IPMsample2.JPG

I just added a date picker to the page and it generates the report for whatever Month / Year selected.

I would still be interested to know if it's possible to do in Power BI.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 56 members 1,072 guests
Please welcome our newest community members: