Counting Members in an Insurance Payor -- Cumulative by Month

[Karolina411]

Hello all

So --I have a dataset with member if, Start Date and End date and want to count each member by month --not JUST when the JOINED on the start date but all of them are supposed to be counted by Month.  Like when we are members of our insurance groups for 5 years we are part of a group.  here is what it looks like.  1 Member id with the start data and end date but I want a count for each member id that was in the month since 2020?  Thank you in advance.  I already tried this formula:PatientsMonthly = CALCULATE(COUNTROWS(PatientDim),FILTER(MemberMonths,
[StartDate]<=STARTOFMONTH(DATE_DIM[DateValue])&&[EndDate]>=STARTOFMONTH(DATE_DIM[DateValue]))) but it only counted each member 1 x--when they started.  There should be like 20k+ in each month but am getting only 125, 210, etc --

[Karolina411]

I must account for each month though.  I need to generate a month for each member per month so 1 member id, if enrolled for 24 months, would have 24 months assigned.  does that make sense?

[v-zhouwen-msft · Accepted solution]

Hi @Karolina411 ,

If I understand correctly, it's '12/31/2078' if there's no explicit end date? I assumed some data.

Please follow these steps:

1.Use the following DAX expression to create a column in ‘Table’

Column = DATE(YEAR([EndDate]),MONTH([EndDate]),1)-1

2.Use the following DAX expression to create a table named 'Table2'

Table 2 = 
ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2024,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]))

3..Use the following DAX expression to create columns in 'Table2'

Column = ENDOFMONTH('Table 2'[Date])

Column 2 = STARTOFMONTH('Table 2'[Date])

 4.Use the following DAX expression to create a measure

Measure = 
 VAR _a = SELECTEDVALUE('Table 2'[Column 2])
 VAR _b = SELECTEDVALUE('Table 2'[Column])
 RETURN COUNTROWS(FILTER('Table','Table'[StartDate] <= _a && 'Table'[Column] >= _b))

5.Final output

[v-zhouwen-msft]

Hi @Karolina411 ,

Can you provide some simple data and show the expected results as a picture?

[Karolina411]

@v-zhouwen-msft Here is what my dataset looks like: I want to count each row IN BETWEEN Dates using Dax so if a Member has a date where they have ended then I want that not in that month as they are no longer a member of that group. Does that make sense?  if they are still a member the date is 12/31/78.