Forum Discussion
jk_adelaide
2 years agoFrequent Visitor
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 = CA...
- 2 years ago
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
- 2 years ago
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)
Ahmedx
Super User
2 years ago