open Claims monthly count

[jk_adelaide]

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

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26Rimc1BFjek33M-f9D?e=OAgThj

[Ahmedx]

[Ahmedx · Accepted solution]

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_adelaide]

Thanks Ahmedx, appriciated.👍

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26Rimc1BFjek33M-f9D?e=OAgThj

[jk_adelaide]

Hi Ahmedx,

thanks but i can't open this with my version of power bi.