Forum Discussion
Solved
open Claims monthly count
Hi,
I want to count the open claims from the below table. Any claims not closed will carry to the next month as open.
i used the below measure but not giving accurate data.
Total open Claims = CALCULATE(COUNT('Claims'[Claims ID]),FILTER('Claims','Claims'[Created]<=ENDOFMONTH('Date'[Date]) && 'Claims'[Closed]>=ENDOFMONTH('Date'[Date])))
i have created a inactive relationship with date table with Claims Created and Closed.
| Claims ID | Created | Closed |
| 1909260948 | 28/09/2022 | 17/05/2023 |
| 1909271500 | 17/10/2022 | 28/03/2023 |
| 1909281045 | 27/10/2022 | 28/03/2023 |
| 1909291339 | 03/11/2022 | 28/03/2023 |
| 1909081108 | 13/12/2022 | 03/02/2023 |
| 1909081323 | 21/12/2022 | 17/02/2023 |
| 1909081123 | 22/02/2023 | 30/05/2023 |
| 1908101218 | 30/03/2023 | 03/04/2023 |
| 1908110817 | 14/04/2023 | 19/05/2023 |
| 1908110852 | 15/05/2023 | 19/05/2023 |
| 1908121007 | 15/05/2023 | 12/07/2023 |
| 1908171021 | 05/06/2023 | 15/06/2023 |
| 1917081223 | 05/06/2023 | 15/06/2023 |
| 1908171811 | 06/06/2023 | 15/06/2023 |
| 1909231812 | 12/06/2023 | 15/06/2023 |
| 1909281639 | 12/06/2023 | 28/06/2023 |
| 1909261146 | 16/08/2023 |
if you do a manual calculations it will be like below but measure gives different. Thanks in advance.
| Month Year | manual | measure |
| Jan-23 | 6 | 6 |
| Feb-23 | 7 | 4 |
| Mar-23 | 6 | 2 |
| Apr-23 | 4 | 2 |
| May-23 | 5 | 1 |
| Jun-23 | 6 | 1 |
| Jul-23 | 1 | no value |
| Aug-23 | 1 | no value |
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
1) create calendar table
Calendar = VAR BaseCalendar = CALENDAR ( DATE ( 2022, 1, 1 ), DATE (2023, 12, 31 )) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1 RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT ( BaseDate, "mmmm"), "Year Month Number", YearMonthNumber, "Year Month", FORMAT ( BaseDate, "mmm yy") ) )2) measure
count the open claims = VAR _GenaretDateBetWeen = GENERATE ( 'Claims', DATESBETWEEN ( 'Calendar'[Date], 'Claims'[Created], IF ( ISBLANK ( 'Claims'[Closed] ), 'Claims'[Created], 'Claims'[Closed] ) )) VAR _SelectColumns_And_Distinct = DISTINCT(SELECTCOLUMNS(_GenaretDateBetWeen, "Claims ID",[Claims ID] , "@Month_Year",FORMAT ([Date], "mmm yy"))) VAR _SelectOnlydateCoumns= SELECTCOLUMNS(_SelectColumns_And_Distinct,"@Month_Year",[@Month_Year]) VAR _INTERSECT = INTERSECT(_SelectOnlydateCoumns,VALUES('Calendar'[Year Month])) RETURN COUNTROWS(_INTERSECT)
5 Replies
1) create calendar table
Calendar = VAR BaseCalendar = CALENDAR ( DATE ( 2022, 1, 1 ), DATE (2023, 12, 31 )) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1 RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT ( BaseDate, "mmmm"), "Year Month Number", YearMonthNumber, "Year Month", FORMAT ( BaseDate, "mmm yy") ) )2) measure
count the open claims = VAR _GenaretDateBetWeen = GENERATE ( 'Claims', DATESBETWEEN ( 'Calendar'[Date], 'Claims'[Created], IF ( ISBLANK ( 'Claims'[Closed] ), 'Claims'[Created], 'Claims'[Closed] ) )) VAR _SelectColumns_And_Distinct = DISTINCT(SELECTCOLUMNS(_GenaretDateBetWeen, "Claims ID",[Claims ID] , "@Month_Year",FORMAT ([Date], "mmm yy"))) VAR _SelectOnlydateCoumns= SELECTCOLUMNS(_SelectColumns_And_Distinct,"@Month_Year",[@Month_Year]) VAR _INTERSECT = INTERSECT(_SelectOnlydateCoumns,VALUES('Calendar'[Year Month])) RETURN COUNTROWS(_INTERSECT)Thanks Ahmedx, appriciated.👍
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Hi Ahmedx,
thanks but i can't open this with my version of power bi.
