HI,
I have this requirement where I need to calculate count of members per month based on start and end dates.
ex: If a member's plan start on 01/01/2021 and plan ends on 03/31/2021 then this member has to be counted in Jan member counts, Feb member counts and march.
So, I have a members table with columns "member_id", "plan start date" and "plan end date" and a date dimension table which I created in power bi data model with columns "date", "year", "month", "year-month."
In the modeling tab I created a relationship on "Plan start date" in members table and "date" on date dimension with cardinality many to many and cross filter direction to both(not sure if its right).
I have created a seperate measure to calculate the member count"
MemberCount = Calculate(DISTINCTCOUNT(member[member_Id]),FILTER(member,member[plan start date]<= CALCULATE(MAX('date dimension'[Date])) && member[plan end date]>= CALCULATE((MIN('date dimension'[Date]))))).
In the reports tab when I drag this measure and year-month, I am not getting correct member counts, as per above example a member is getting counted in the month of Jan and not in Feb and Mar.
Please let me know where I am wrong and direct me the correct process for this requirement. your help is greatly appreciated.
