Calculate monthly revenue by dividing client budget by duration of project in months

[Anonymous]

Hi,

I am new to Power BI. First of all, for the visualisation I am sharing the table columns which has an effect on the formula I would like to make in Power BI:

I have been trying to figure out how to deal with the following formula:

I would like to be able to calculate and insert the budget per month of the project.

So in the above situation, the follow is the case:

Project Start: 19th of January 2020

Duration in Days: 35

Project Finish: 23rd of February

Include in financial forecast: YES

Client Budget: 200.000

So ideally the formula should be able to distinguish:

IF('Include in financial forecast'= "Yes" THEN (Client Budget) / (The total month that the project spans over - in this case two month (January and February))

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a table to test:

Please take following steps:

1)Create a column:

Column = 
IF (
    'Table'[Include in financial forecast] = "Yes",
    DIVIDE (
        'Table'[EstimatedClientBudget],
        DATEDIFF (
            'Table'[Estimated Start Time],
            'Table'[Estimated Finish Time],
            MONTH
        ) + 1
    ),
    BLANK ()
)

And it shows:

2)Create a calculated table:

Calendar = CALENDAR(MIN('Table'[Estimated Start Time]),MAX('Table'[Estimated Finish Time]))

3)Create a measure to show each month's budget:

Budget per month = 
SUMX (
    ADDCOLUMNS (
        FILTER (
            'Table',
            'Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),
        "AverageBudget", 'Table'[EstimatedClientBudget]
            / (
                DATEDIFF (
                    'Table'[Estimated Start Time],
                    'Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

And it shows:

Best Regards,

Giotto Zhi

[Anonymous]

@v-gizhi-msft 

Apologies for the late response. Thank you very much for the execellent description, it worked!

[JustJan]

Hi @Anonymous ,

You can add a column to the table: 

monthlybudget = if('Projects'[IncludeInForecast]="yes", DIVIDE(Projects[ClientBudget], DATEDIFF(Projects[ProjectStart], Projects[ProjectFinish], month)+1),0)

[Anonymous]

@JustJan 

Hi Jan,

Thank you! The calculation works.

How do I make sure that the calculations then get added to the months that it spans over?

Fx. if a project start in January and spans until February, the budget gets divided by 2, but then I want each of those to get added to those two months revenue.

Hope I am being clear

[JustJan]

Hi @Anonymous , 

I understand what you are looking for, but this requires records to be generated. Your sample record requires (at least) to records.

Unfortunately I am not really familiar with the required m-query commands to generate the records. 

But I am definitely interested in the solution. Should I find a way to deal with it, I'll let you know.

Regards, 

Jan