PowerBI DAX Measure: Sum of SalesAmount in future periods where data provides a single value and start date

I'm working on a revenue forecast using data from Salesforce that provides, for each Opportunity[Id], a close date and a single value for Monthly revenue (Annual revenue is also available if it makes the code easier to read/understand).

I appreciate your time and assistance with this problem.

Data in Model:

Opportunity[Id]	Opportunity[close date]	Opportunity[Monthly Weighted Revenue]
A	2024-09-20	10
B	2024-11-12	40
C	2024-10-24	20

Note: Opportunity[close date] is related to 'Calendar'[Date]

Requirement output #1:

Opportunity[Id]	Jul'24	Aug'24	Sep'24	Q2'24	Oct'24	Nov'24	Dec'24	Q4'24	FY'24
A	0.00	0.00	10.00	10.00	10.00	10.00	10.00	30.00	40.00
B	0.00	0.00	0.00	0.00	0.00	40.00	40.00	80.00	80.00
C	0.00	0.00	0.00	0.00	20.00	20.00	20.00	60.00	60.00
Total	0.00	0.00	10.00	10.00	30.00	70.00	70.00	170.00	180.00

Requirement output #2:

Note: values for Opportunity A contribute to the total in Q4'24 even though it has a close date in Q3'24 which isn't in the current filter context. Other filters such as Opportunity[status] still need to filter the report.

Opportunity[Id]	Q4'24	FY'24
Total	170.00	170.00

Current Code:

My current measure repeats the monthly weighted revenue when rendered in a table visual with columns for Opportunity[Id] and months, however, it doesn't work when I remove Opportunity[Id] and it doesn't work for subtotals. I've tried various iterations of SUMX() over a table that calls the variable "_Monthly_Weighted_Revenue_Fixed_Repeating", but none have worked so far.

MRW extended = 
VAR _CurrentMonth =
    MAX('Calendar'[Date])
VAR _CloseMonth = 
    CALCULATE(
        EOMONTH( MAX(Opportunity[CloseDate] ), 0 ),
        REMOVEFILTERS('Calendar')
    )
VAR _Monthly_Weighted_Revenue_Fixed = // stores the value corresponding with the single close date in a way it can be called from any period.
    CALCULATE(
        [Monthly Revenue Weighted],
        REMOVEFILTERS('Calendar')
    )
VAR _Monthly_Weighted_Revenue_Fixed_Repeating = // this repeats the above value for all future months.
    IF(
        _CurrentMonth >= _CloseMonth,
        _Monthly_Weighted_Revenue_Fixed,
        BLANK()
    )
RETURN _Monthly_Weighted_Revenue_Fixed_Repeating

Update Fri, Aug 21 '24:

I have the below code working tentatively.

Open to refactoring suggestions.
satisfies my two required outputs above, but haven't tested for unexpected results with report filters or slicers.

Monthly Revenue Weighted =
SUMX(
    ADDCOLUMNS(
        CROSSJOIN( // returns a table with all combinations of opportunity[Id] and Date (as EOM)
            CALCULATETABLE( TREATAS ( DISTINCT( Opportunity[Id]), Opportunity[Id] ), REMOVEFILTERS('Calendar') ),
            TREATAS(DISTINCT('Calendar'[Date as EOM]),'Calendar'[Date])
        ),
        "closeDate", CALCULATE( EOMONTH(MAX(Opportunity[CloseDate]),0), REMOVEFILTERS('Calendar') ),
        "MRW" ,
        IF( // returns monthly weighted revenue beginning on the close date and continuing into future periods.
            'Calendar'[Date] >= CALCULATE( EOMONTH(MAX(Opportunity[CloseDate]),0), REMOVEFILTERS('Calendar') ),
            CALCULATE(
                [Annual Revenue Weighted] / 12,
                REMOVEFILTERS('Calendar')
            ),
            BLANK()
        )
    ),
    [MRW]
)

Solution

Update Fri, Aug 21 '24: I have the below code working tentatively.

Monthly Revenue Weighted =
SUMX(
    ADDCOLUMNS(
        CROSSJOIN( // returns a table with all combinations of opportunity[Id] and Date (as EOM)
            CALCULATETABLE( TREATAS ( DISTINCT( Opportunity[Id]), Opportunity[Id] ), REMOVEFILTERS('Calendar') ),
            TREATAS(DISTINCT('Calendar'[Date as EOM]),'Calendar'[Date])
        ),
        "closeDate", CALCULATE( EOMONTH(MAX(Opportunity[CloseDate]),0), REMOVEFILTERS('Calendar') ),
        "MRW" ,
        IF( // returns monthly weighted revenue beginning on the close date and continuing into future periods.
            'Calendar'[Date] >= CALCULATE( EOMONTH(MAX(Opportunity[CloseDate]),0), REMOVEFILTERS('Calendar') ),
            CALCULATE(
                [Annual Revenue Weighted] / 12,
                REMOVEFILTERS('Calendar')
            ),
            BLANK()
        )
    ),
    [MRW]
)