Budget Distribution

Hi aboshabo ,

You can try to use following measure formula to calculate each year budget based on current ContractN, year.

Measure =
VAR maxdate =
    MAX ( 'Table'[Date] )
VAR summary =
    SUMMARIZE (
        ContractReportExcel,
        [ContractN],
        [Amount],
        [Contract Start Date],
        [Contract End Date],
        "duration", DATEDIFF ( [Contract Start Date], [Contract End Date], MONTH )
    )
VAR unit =
    SUMX (
        FILTER (
            summary,
            maxdate IN CALENDAR ( [Contract Start Date], [Contract End Date] )
                && [ContractN] IN VALUES ( ContractReportExcel[ContractN] )
        ),
        [Amount] / [duration]
    )
VAR _calendar =
    ADDCOLUMNS (
        CALENDAR (
            CALCULATE (
                MIN ( ContractReportExcel[Contract Start Date] ),
                ALLSELECTED ( ContractReportExcel ),
                VALUES ( ContractReportExcel[ContractN] )
            ),
            CALCULATE (
                MAX ( ContractReportExcel[Contract End Date] ),
                ALLSELECTED ( ContractReportExcel ),
                VALUES ( ContractReportExcel[ContractN] )
            )
        ),
        "Month", MONTH ( [Date] )
    )
VAR _duration =
    COUNTROWS (
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( _calendar, YEAR ( [Date] ) = YEAR ( maxdate ) ),
                "M", [Month]
            )
        )
    )
RETURN
    unit * _duration

Notice: Table is a calendar I created for calculate, it no has relationship to original table.

I also attached sample file below.

Regards,

Xiaoxin Sheng