Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisN
New Member

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
ChrisN
New Member

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.

View solution in original post

4 REPLIES 4
ChrisN
New Member

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.

Greenwoodr
Helper I
Helper I

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?

v-yuta-msft
Community Support
Community Support

@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.

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.