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
2 years agoSuper User
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)
- jk_adelaide2 years agoFrequent Visitor
Thanks Ahmedx, appriciated.👍