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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jharris32
Frequent Visitor

Calculating all cases open for each month

Hi I have case data that includes open and close date (as well as cases where there is no close date yet) and I want to be able to show how many cases were open each month - that could be cases that have opened in the month, but also needs to include cases that opened in prior months AND cases that closed in that month. 

 

The data I have looks like this:

 Case IDOpen DateClose Date
 a1/1/202311/15/2023
 b1/19/2024 
 c12/20/2022 
 x10/15/202312/2/2023
 y7/4/2022 
 z1/2/20241/20/2024

 

This is what I want to be able to show:

MonthCases Open
November 20233
December 20233
January 20244
4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @jharris32 ,

 

Not quite sure how you came up with Cases Open in your expected results, but based on your description, there are more than just three months that hold results, so you can check out my methodology.

 

Here are the steps you can follow:

1. Create calculated table.

Table 2 =
var _table=
CALENDAR(
    DATE(2022,1,1),
    DATE(2024,2,1))
return
ADDCOLUMNS(
    _table,
    "Month_Year",FORMAT([Date],"mmmm")&" "&YEAR([Date]))

vyangliumsft_0-1706070671249.png

2. Create calculated column.

Test =
var _test=
FILTER(
    'Table 2',
    IF('Table'[Close Date]<>BLANK(),
    'Table 2'[Date]>=EARLIER('Table'[Open Date])&&'Table 2'[Date]<=EARLIER('Table'[Close Date]),
    'Table 2'[Date]>=EARLIER('Table'[Open Date])&&'Table 2'[Date]<=EOMONTH(EARLIER('Table'[Open Date]),0)))
var _table=
SUMMARIZE(
    _test,[Month_Year])
return
CONCATENATEX(
    _table,[Month_Year],"-")

vyangliumsft_1-1706070671251.png3. Create measure.

Cases Open =
COUNTX(
    FILTER(ALL('Table'),
    CONTAINSSTRING(
       'Table'[Test] ,MAX('Table 2'[Month_Year]))=TRUE()),[Case ID])

4. Result:

vyangliumsft_2-1706070714931.png

 

 

Best Regards,

Liu Yang

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

Hi Lui! I got to those numbers because during those months that's how many active cases were open. So if "case y" opened in July 2022, but has not closed it should be counted in the Cases Opened column in November 2023/December 2023/January 2024 because it is still open in all of those months. I don't need a count of what had an open date in those months. So I guess the better way to phrase it would be a count of active cases month to month. Does this process work for that?

Idrissshatila
Super User
Super User

Hello @jharris32 ,

 

how did you get the result in the expected outcome based on the data you've provided ?

 

like where did the 3, 3 ,4 come from.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Hi! Thanks for the question. I got to those numbers because during those months that's how many active cases were open. So if "case y" opened in July 2022, but has not closed it should be counted in the Cases Opened column in November 2023/December 2023/January 2024 because it is still open in all of those months. I don't need a count of what had an open date in those months. So I guess the better way to phrase it would be a count of active cases month to month.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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