Budget Distribution

[aboshabo]

I hope you are doing well!

I have a question about budgeting issue, I need your help. Thanks for your support in advance!

I have three columns, Start Date, End Data and Amount of the budget.

I have to distribute the amount to years based on the contract duration (months and year(s)). For example, I have a contract start 1/8/2019, end 1/1/2023, amount is 302500, so I need to divide the amount to three years and five months. So the budget of 2019 well be 36890, and each years the budget amount is 88537.

A sample data in the follwoing file (URL).

https://www.dropbox.com/s/trynlxzkhcs0tnw/Budget%20Distribution.pbix?dl=0

Is this possible in Power BI?

Thanks for your support in advance!

Best Regards

Mahmoud

[v-shex-msft · Accepted solution]

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