Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Michael Thoman]

Good Day, 

I need a way to show the total Member Months (Sum of active members by month) by group. 
I have a member list table that contains the effective date and termination date of all members and includes their group ID. 
On my report, I two date slicers, one for year and the other for month name.

My thought process (and I may be terribly off) is to use a 3 variables (

1. MinDate should equal the earliest date in my ‘Dates’ table (adjusted by the filter)
2. MaxDate should equal the latest date in my ‘Dates’ table (adjusted by the filter)
3 MemberMonthsList hold the member list information and calculate the datediff between the latest of Coverage_Start_date and MinDate and the earliest of the Coverage_End_Date and MaxDate. 

As an example, we want to calculate the total member months from March 2020 through April 2020
Member ID       Effective Date     Termination Date  Member Months
000015115       2020-01-01         9999-12-31           2 (Active for both March and April)
000013658       2020-01-01         2020-03-31           1 (Active for only March)
000015858       2020-04-01         9999-12-31           1 (Active for only April)

Member Months for this group would equal 4.

Here is a snap shot of my current measure. This appears to work for some groups by not all. Including some that return a negative number which leads me to believe that either the Filter on the MemberList table is wrong or the datediff is wrong 

Member Months =

VAR MinDate = MIN(‘Dates'[Date])

VAR MaxDate = MAX(‘Dates'[Date])

VAR MemberMonthsList =

ADDCOLUMNS(FILTER(‘Memberlist’,MemberList[Coverage_Start_Date] <= MaxDate && ‘MemberList'[Coverage_End_Date] >= MinDate),

        “DATEDIFF”,

        DATEDIFF(

            IF(MemberList[Coverage_Start_Date] <= MinDate, MinDate, MemberList[Coverage_Start_Date]),

            IF(MemberList[Coverage_End_Date] >= MaxDate, MaxDate, MemberList[Coverage_Start_Date]),MONTH)+1)

RETURN

SUMX(MemberMonthsList, [DATEDIFF])

Any help would be greatly appreciated. 

Cheers

——————————
Michael Thoman

——————————