Forum Discussion

jk_adelaide's avatar
jk_adelaide
Frequent Visitor
2 years ago
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 = CA...
  • Ahmedx's avatar
    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)