Is there a way to extrapolate a 12 month total fee into a monthly fee across multiple clients with different start dates?

Question

This is my first post on this, so please excuse me if I don't go into enough detail, but I'll try my best.

My problem is as per the title, I want to extrapolate fee data from one of my tables across the 12 month period that the contract is active.

I have one column in the "Merged - Service Period" table that represents a total fee that we expect to receive over 12 months. This fee sits under an individual record/row, that also has data such as the name associated with the contract and the start and end date of the contract.

I want to create another table that extrapolates this fee over the next twelve months i.e. if a start date is within the month of Jan, I want to have a column for each month of the year that has the monthly fee (the total fee divided by 12) in each month, and then zero for any other month before the start date and after the 12 months. However, often the customer renews their contract, so in that same row, if a new fee is arranged, I want the updated expected fee.

For clarification, this table has a many to one relationship with a client table where we store client data, which is connected via a client ID if that helps.

I need the client ID's to be the first column, and then the dates to be the following columns (i.e. 202201, 202202, 202203, etc.)

My current attempt at doing this was through a date table and the Service Period table.

Here is an example of all of the important details in the Service Period table:

serviceperiodID	OwnerID	OwnerName	Statecodename	Startdate	Enddate	Serviceperiodname	HouseholdID	totalfee	totalfee x gst	Current Stage Name
AD123-15GAT-OPSSS	C20R2-991MN-LOP92	Chris R	Inactive	25/02/2023	24/02/2024	Smith, Sam - 25/2/2023 to 24/2/2024	LOIF7-JGN25-453JG	$1,100	$1,000.00	Service Period Inactive
HSB94-875JG-6G8GD	C20R2-991MN-LOP92	Chris R	Active	25/02/2024	25/02/2025	Smith, Sam - 25/2/2024 to 24/2/2025	LOIF7-JGN25-453JG	$2,200	$2,000.00	Service Period Active
HG049-Y78JF-LAK25	C20R2-991MN-LOP93	Chris R	Active	14/06/2023	13/06/2024	Spears, Britney - 14/6/2023 to 13/6/2024	JJK83-IOP0S-MG674	$3,300	$3,000.00	Service Period Active
NAUS5-JS94B-VT6R7	C20R2-991MN-LOP94	Chris R	Inactive	20/04/2022	19/04/2023	Spears, Britney - 20/4/2022 to 19/4/2023	JJK83-IOP0S-MG674	$2,200	$2,000.00	Service Period Inactive
BHY24-HGF24-JGN51	C20R2-991MN-LOP95	Chris R	Inactive	5/01/2023	4/01/2024	Swift, Taylor - 5/1/2023 to 4/1/2024	NNBS2-GJS6T-578TF	$5,500	$5,000.00	Service Period Inactive

This is my attempt at making this table. Not sure if I am coming at this from the right angle, but would love some guidance/help.

  ExtrapolatedTable = 
  ADDCOLUMNS (
     CROSSJOIN (
        VALUES ( 'Merged - Service Period'[finpal_householdid] ),
        VALUES ( 'Date Table'[YearMonth] )
     ),
     "MonthlyFee", 
     IF (
        CALCULATE (
           COUNTROWS ( 'Service Periods' ),
        ),
        DIVIDE ( RELATED ( **'Merged - Service Period'[Fees Exc. GST]** ), 12 ),
        IF (
           AND (
              'Date Table'[YearMonth] >= 'Merged - Service Period'**[StartMonthYear]**,
              'Date Table'[YearMonth] < DATEADD ( 'Merged - Service Period'[StartMonthYear], 12, MONTH )
           ),
           DIVIDE ( RELATED ( **'Merged - Service Period'[Fees Exc. GST]** ), 12 ),
           0
        )
     )
  )

I am getting errors showing up where I have bolded the DAX code above

Anything that has "YearMonth" is currently in the 'YYYY-MM' format. And "householdid" is a string containing letters and numbers.

I am expecting to get an output something along the lines of the following:

HouseholdID	202201	202202	202203	202204	202205	202206	202207	202208	202209	202210	202211	202212	202301	202302	202303	202304	202305	202306	202307	202308	202309	202310	202311	202312	202401	202402	202403	202404	202405	202406	202407	202408	202409	202410	202411	202412	202501
LOIF7-JGN25-453JG	0	0	0	0	0	0	0	0	0	0	0	0	0	83.33333	83.33333	83.33333	83.33333	83.33333	83.33333	83.33333	83.33333	83.33333	83.33333	83.33333	83.33333	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667
JJK83-IOP0S-MG674	0	0	0	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	166.6667	0	0	250	250	250	250	250	250	250	250	250	250	250	250	0	0	0	0	0	0	0	0
NNBS2-GJS6T-578TF	0	0	0	0	0	0	0	0	0	0	0	0	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	0	0	0	0	0	0	0	0	0	0	0	0	0

Maybe it would be a better approach to do this in a matrix? But I am not sure how I would achieve this.

Answer 1

You can accomplish this with a Measure instead of a Calculated Table.

Create a new Measure similar to:

Monthly Fee = 
  SUMX(
    DISTINCT('Merged - Service Period'[finpal_householdid]),
    (
      var minD = MIN('Date Table'[Date])
      var maxD = MAX('Date Table'[Date])
      var top1 = 
        CALCULATETABLE(TOPN(1,
          FILTER(
            'Merged - Service Period',
            [Startdate] <= maxD && [Enddate] >= minD
          ),
          [Startdate], DESC
        ))
      var fee = CALCULATE(MAX('Merged - Service Period'[Fees Exc. GST]), top1)
      var result = DIVIDE(fee, 12)

      return COALESCE(result, 0)
    )
  )

Then use a Matrix visual with:

• Rows: 'Merged - Service Period'[finpal_householdid]
• Columns: 'Date Table'[YearMonth]
• Values: [Monthly Fee]

Note: You will get a Can't display the visual error, this will go once the measure is added to the Values.

---

Supplemental
The above assumes there isn't a relationship between Date Table and Merged - Service Period tables. If you do have a relationship, then either remove it, or, use the following version of the measure:

Monthly Fee = 
  SUMX(
    CALCULATETABLE(
      DISTINCT('Merged - Service Period'[finpal_householdid]),
      REMOVEFILTERS('Date Table')
    ),
    (
      var minD = MIN('Date Table'[Date])
      var maxD = MAX('Date Table'[Date])
      var top1 = 
        CALCULATETABLE(TOPN(1,
          FILTER(
            'Merged - Service Period',
            [Startdate] <= maxD && [Enddate] >= minD
          ),
          [Startdate], DESC
        ), REMOVEFILTERS('Date Table'))
      var fee = CALCULATE(MAX('Merged - Service Period'[Fees Exc. GST]), top1)
      var result = DIVIDE(fee, 12)

      return COALESCE(result, 0)
    )
  )