Total Measure - Counting with Unrelated Date Table

[rjhale]

Looking for some help with this measure.  We have data that looks like this: 

COMMITMENT_START_DATE	COMMITMENT_END_DATE	COMMITMENT_TOTAL_PCT	NAME	PERSON_ID	COMMITMENT_DATE_LAST_UPDATED	ProjectID	ProjectStatus
8/15/2021	7/14/2022	6.25	Doe, John	005555	7/12/2021	AAJ5178	1-Open
9/15/2009	8/31/2013	8.00	Doe, John	005555	8/3/2012	PRJ29MW	5-Closed
9/1/2013	8/31/2017	10.00	Doe, John	005555	6/22/2016	PRJ75YR	6-Closed - Archived

We want to display a matrix that shows every month what that person's commitment to the project is based on the start and end dates in the first two columns.  We created a date table (this is not related to the commitments table above) and wrote a measure like this: 

Commitment Total % =
VAR endOfPeriod =
    MAX ( 'Date'[Date] )
VAR startOfPeriod =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'COMMITMENTS'[COMMITMENT_TOTAL_PCT] ) / 100,
        'COMMITMENTS'[COMMITMENT_START_DATE] <= endOfPeriod,
        'COMMITMENTS'[COMMITMENT_END_DATE] >= startOfPeriod
    )

That works, and we get a nice breakdown like this: 

Now I need to update the Total Row to count the number of months each person had a commitment.  In the above example, we should be totaling up to 108 (12 months + 48 months + 48 months).  I figured out how to change the grand total row total using a conditional statement with the function HasOneValue, but I'm lost as to how to count the unique year+month combination in the total line. 

Any thoughts? 

[rjhale · Accepted solution]

Ok.  I think I figured this out after a lot of trial and error.  I'm posting my own solution in hopes that it helps someone else down the road.  I started by creating a summarization table that incorporates the measure I mentioned in my first post:

Commitments by Month = 
   ADDCOLUMNS(Filter(SUMMARIZECOLUMNS(
      'COMMITMENTS'[ProjectID],
      'COMMITMENTS'[NAME],
      'COMMITMENTS'[PERSON_ID],
      'COMMITMENTS'[PROJECTS_KEY],
      'COMMITMENTS'[Active Commitment],
      'COMMITMENTS'[Fund],
      'COMMITMENTS'[ProjectStatus],
      'Date'[Year+Month],
      "Commitment Total %", '_Measures'[Commit Total %],
    ), 'COMMITMENTS'[NAME] <> BLANK()), "Count", 1)

After that, I created a new measure for formatting the total line slightly differently from the individual rows:

Commitment Total-Formatted =
IF (
    HASONEVALUE ( 'Commitments by Month'[Year+Month] ),
    SUM ( 'Commitments by Month'[Commitment Total %] ),
    FORMAT ( SUM ( 'Commitments by Month'[Commitment Total %] ), "Fixed" )
)

The summarization table was the key to getting this to work as expected.  

[daXtreme]

// Unique combinations:

SUMMARIZE(
    'Table',
    'Table'[Column1],
    'Table'[Column2],
    ...
    'Dim1'[Col1],
    ...
    'Dim2'[Col1],
    ...
)

// If a dim is connected to a fact table via one-to-many,
// then you can also group by the columns of the dim.

[rjhale]

I'm not sure I follow this.  In my post, I mentioned that the data table isn't connected to my commitments table.  I can't think of how I would relate the two tables because I'm just trying to show what someone's commitment is for every month that the project is open.  The percentage doesn't change for the length of the project.