Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"