Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chintudwh
New Member

Count of members per month based on start and end dates and date dimension

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.
0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors