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
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 ID | Open Date | Close Date | |
a | 1/1/2023 | 11/15/2023 | |
b | 1/19/2024 | ||
c | 12/20/2022 | ||
x | 10/15/2023 | 12/2/2023 | |
y | 7/4/2022 | ||
z | 1/2/2024 | 1/20/2024 |
This is what I want to be able to show:
Month | Cases Open |
November 2023 | 3 |
December 2023 | 3 |
January 2024 | 4 |
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]))
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],"-")
3. Create measure.
Cases Open =
COUNTX(
FILTER(ALL('Table'),
CONTAINSSTRING(
'Table'[Test] ,MAX('Table 2'[Month_Year]))=TRUE()),[Case ID])
4. Result:
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?
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |