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))

[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))

[Jimmy801]

Hello @Anonymous 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[dax]

Hi @Anonymous ,

You could refer to below M code to see whether it work or not. If this is not what you want, please  correct me and inform your expected output.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlHSUTLUN7TUNzIwMgCyjU2BhJG+kTFMINI1GCRiAARKsTrRSiVAPUZgEaAmU31DMBPBNwIJ+PmDTIVoiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Start" = _t, #"Duration in days" = _t, #"Project finish" = _t, #"Include in financial forecast" = _t, #"Client Budget" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project Start", type date}, {"Duration in days", Int64.Type}, {"Project finish", type date}, {"Include in financial forecast", type text}, {"Client Budget", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Include in financial forecast]="YES"  then [Client Budget]/(Date.Month([Project finish])-Date.Month([Project Start])+1) else 0)
in
    #"Added Custom"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @Anonymous 

try out this solution. It uses a custom function to calculate the difference between two dates (adding 1, includes both months)

let
	Source = #table
	(
		{"Project","Project Start","Duration in days","Project finish","Include in financial forecast","Client Budget"},
		{
			{"Test","43849","35","43884","YES","20000"}
		}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Client Budget", Int64.Type}}),

	ToDate = Table.TransformColumns
	(
		ChangeType,
		{
			{
				"Project Start",
				each Date.From(Number.From(_)),
				type date
			},
			{
				"Project finish",
				each Date.From(Number.From(_)),
				type date
			}
		}
	),
	// CalculateDifferenceMonth
	DifferenceMonth = (datetocalculate as date, datefromcalculate as nullable date) as number =>
	let
		datefromcalculateinternal = if datefromcalculate = null then DateTime.FixedLocalNow() else datefromcalculate,
		
		Monthcalc = Date.Month(Date.From(datetocalculate)),
		Yearcalc = Date.Year(Date.From(datetocalculate)),
		Monthcurr = Date.Month( datefromcalculateinternal),
		Yearcurr = Date.Year(datefromcalculateinternal),

		MonthDiff =(Monthcalc-Monthcurr), // -
		YearDiffinMonths = ((Yearcurr-Yearcalc)*12),
		Result = MonthDiff  - YearDiffinMonths
	in
		Result+1,

    AddDifferenceMonth = Table.AddColumn(ToDate, "BudgetMonth", each if [Include in financial forecast]="YES" then [Client Budget]/(DifferenceMonth([Project finish], [Project Start])) else 0)
in
	AddDifferenceMonth

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 @dax ,

Thank you both for you assistance.

I made a column in which I wrote the following DAX formula:

monthlybudget = if('Sales Pipeline'[Include in financial forecast]="Yes", DIVIDE('Sales Pipeline'[Current Client Budget], DATEDIFF('Sales Pipeline'[Estimated Start Time], 'Sales Pipeline'[Estimated Finish Time], month)+1),0)

This works the same as the M language you have written.

However, I do not know how I make Power BI "understand" that I want the "MonthlyBudget" to be added PER project PER month it spans over.

For example, in the test example you made @Jimmy801 , it spans from January till February. Hence if I connect to the Calender table I have, I would like for it to show up as revenue for those months.

[Jimmy801]

Hello @Anonymous 

so you would have not to calculate the monthly budget, but create a Record/Table with this calculation and then expand it

Jimmy

[Anonymous]

@Jimmy801 

You you be able to ellaborate and guide me in the right direction?

[Jimmy801]

Hello @Anonymous 

would this solution fit your needs

let
	Source = #table
	(
		{"Project","Project Start","Duration in days","Project finish","Include in financial forecast","Client Budget"},
		{
			{"Test","43849","35","43884","YES","20000"}
		}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Client Budget", Int64.Type}}),

	ToDate = Table.TransformColumns
	(
		ChangeType,
		{
			{
				"Project Start",
				each Date.From(Number.From(_)),
				type date
			},
			{
				"Project finish",
				each Date.From(Number.From(_)),
				type date
			}
		}
	),
	// CalculateDifferenceMonth
	DifferenceMonth = (datetocalculate as date, datefromcalculate as nullable date) as number =>
	let
		datefromcalculateinternal = if datefromcalculate = null then DateTime.FixedLocalNow() else datefromcalculate,
		
		Monthcalc = Date.Month(Date.From(datetocalculate)),
		Yearcalc = Date.Year(Date.From(datetocalculate)),
		Monthcurr = Date.Month( datefromcalculateinternal),
		Yearcurr = Date.Year(datefromcalculateinternal),

		MonthDiff =(Monthcalc-Monthcurr), // -
		YearDiffinMonths = ((Yearcurr-Yearcalc)*12),
		Result = MonthDiff  - YearDiffinMonths
	in
		Result+1,

    AddDifferenceMonth = Table.AddColumn(ToDate, "BudgetMonth", each if [Include in financial forecast]="YES" then [Client Budget]/(DifferenceMonth([Project finish], [Project Start])) else 0),
	AddMonthTable = Table.AddColumn(
		AddDifferenceMonth,
		"MonthList",
		(add)=>  List.Transform({1..DifferenceMonth(add[Project finish], add[Project Start])}, each Date.AddMonths(Date.StartOfMonth(add[Project Start]),_-1))),
    ExpandMonthRows = Table.ExpandListColumn(AddMonthTable, "MonthList"),
    ChangeType2 = Table.TransformColumnTypes(ExpandMonthRows,{{"MonthList", type date}})
in
	ChangeType2

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 

I am trying to test whether it would work in my own table. However, I am getting an error:

This is how I tried to append the M formula you have written:

let
    Maintable_SPM = let
    Source = Excel.Workbook(Web.Contents("https://dogmaconsulting-my.sharepoint.com/personal/anders_jensen_dogmaalares_com/Documents/PowerApps/SPM%20for%20PowerApp%20Excelsheet.xlsx"), null, true),
    Maintable_SPM_Table = Source{[Item="Maintable_SPM",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Maintable_SPM_Table,{{"Last Updated", type datetime}, {"Pillars", type text}, {"Practice", type text}, {"Account", type text}, {"Industry", type text}, {"Phase", type text}, {"Opportunity Name", type text}, {"Decision Maker / Sponsor Name", type text}, {"Attitude Towards DA", type text}, {"Opportunity in Short", type text}, {"Estimated Start Time", type datetime}, {"DurationInDays", Int64.Type}, {"Estimated Finish Time", type datetime}, {"SpanningOverMonths", type any}, {"Include in financial forecast", type text}, {"WinRatio", Int64.Type}, {"WinRatio2", Int64.Type}, {"Pen Holder", type text}, {"Responsible Partner / Principle", type any}, {"Senior Advisor", type text}, {"Sales Responsible", type any}, {"TechPartners", type text}, {"Reason for Cancel/On hold/Lost", type text}, {"EstimatedClientBudget", Int64.Type}, {"FirstPrice", Int64.Type}, {"LatestPrice", Int64.Type}, {"FinalPrice", type any}, {"CurrentBudget", Int64.Type}, {"WeightedRevenue", Int64.Type}, {"Expenses", Int64.Type}, {"Jan2020", Int64.Type}, {"Feb2020", type number}, {"Mar2020", type number}, {"Apr2020", type number}, {"May2020", type number}, {"Jun2020", type number}, {"Jul2020", type number}, {"Aug2020", type number}, {"Sep2020", Int64.Type}, {"Oct2020", Int64.Type}, {"Nov2020", Int64.Type}, {"Dec2020", Int64.Type}, {"Jan2021", Int64.Type}, {"Feb2021", Int64.Type}, {"Mar2021", Int64.Type}, {"Apr2021", Int64.Type}, {"May2021", Int64.Type}, {"Jun2021", Int64.Type}, {"Jul2021", Int64.Type}, {"Aug2021", Int64.Type}, {"Sep2021", Int64.Type}, {"Oct2021", Int64.Type}, {"Nov2021", Int64.Type}, {"Dec2021", Int64.Type}, {"__PowerAppsId__", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Last Updated", "Opportunity in Short", "SpanningOverMonths", "WinRatio"}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"0 - No Pillars",Replacer.ReplaceValue,{"Pillars"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Pillars", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Pillars.1", "Pillars.2", "Pillars.3", "Pillars.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pillars.1", type text}, {"Pillars.2", type text}, {"Pillars.3", type text}, {"Pillars.4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Last Updated", "Practice", "Account", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "DurationInDays", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "WinRatio", "WinRatio2", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "EstimatedClientBudget", "FirstPrice", "LatestPrice", "FinalPrice", "CurrentBudget", "WeightedRevenue", "Expenses", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns1",{{"Value", Text.Trim, type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Value", "Value - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Select(List.Distinct(#"Duplicated Column"[#"Value - Copy"]), each _ <> ""), "Value - Copy", "Value", List.Max),
    NewHeaders = List.Transform(Table.ColumnNames(#"Pivoted Column"), (ColumnName) => {ColumnName,try Splitter.SplitTextByAnyDelimiter({" -"})(ColumnName){0} otherwise ColumnName})
in
    #"Pivoted Column",
    #"Reordered Columns" = Table.ReorderColumns(Maintable_SPM,{"Last Updated", "Practice", "Account", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "DurationInDays", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "WinRatio", "WinRatio2", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "EstimatedClientBudget", "FirstPrice", "LatestPrice", "FinalPrice", "CurrentBudget", "WeightedRevenue", "Expenses", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__", "0 - No Pillars", "1 - Service Design", "2 - Machine Learning", "3 - Strategy", "4 - IT"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"0 - No Pillars", "0 - No Pillars"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Last Updated", "0 - No Pillars", "1 - Service Design", "2 - Machine Learning", "3 - Strategy", "4 - IT", "Practice", "Account", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "DurationInDays", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "WinRatio", "WinRatio2", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "EstimatedClientBudget", "FirstPrice", "LatestPrice", "FinalPrice", "CurrentBudget", "WeightedRevenue", "Expenses", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Last Updated"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns",{"Account", "0 - No Pillars", "1 - Service Design", "2 - Machine Learning", "3 - Strategy", "4 - IT", "Practice", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "DurationInDays", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "WinRatio", "WinRatio2", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "EstimatedClientBudget", "FirstPrice", "LatestPrice", "FinalPrice", "CurrentBudget", "WeightedRevenue", "Expenses", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns2",{{"Account", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Phase] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Opportunity in Short", "SpanningOverMonths"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Dec2021", Currency.Type}, {"Nov2021", Currency.Type}, {"Oct2021", Currency.Type}, {"Sep2021", Currency.Type}, {"Aug2021", Currency.Type}, {"Jul2021", Currency.Type}, {"Jun2021", Currency.Type}, {"May2021", Currency.Type}, {"Apr2021", Currency.Type}, {"Mar2021", Currency.Type}, {"Feb2021", Currency.Type}, {"Jan2021", Currency.Type}, {"Dec2020", Currency.Type}, {"Nov2020", Currency.Type}, {"Oct2020", Currency.Type}, {"Sep2020", Currency.Type}, {"Aug2020", Currency.Type}, {"Jul2020", Currency.Type}, {"Jun2020", Currency.Type}, {"May2020", Currency.Type}, {"Apr2020", Currency.Type}, {"Mar2020", Currency.Type}, {"Feb2020", Currency.Type}, {"Jan2020", Currency.Type}, {"Expenses", Currency.Type}, {"WeightedRevenue", Currency.Type}, {"CurrentBudget", Currency.Type}, {"FinalPrice", Currency.Type}, {"LatestPrice", Currency.Type}, {"FirstPrice", Currency.Type}, {"EstimatedClientBudget", Currency.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"0 - No Pillars", "No Pillars"}, {"1 - Service Design", "Service Design"}, {"2 - Machine Learning", "Machine Learning"}, {"3 - Strategy", "Strategy"}, {"4 - IT", "IT"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"WinRatio"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"WinRatio2", "Win Ratio in %"}, {"CurrentBudget", "Current Client Budget"}, {"WeightedRevenue", "Weighted Revenue"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns3",{{"Estimated Start Time", type date}, {"Estimated Finish Time", type date}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"Include in financial forecast", Order.Descending}}),


    // CalculateDifferenceMonth

	DifferenceMonth = (datetocalculate as date, datefromcalculate as nullable date) as number =>
	let
		datefromcalculateinternal = if datefromcalculate = null then DateTime.FixedLocalNow() else datefromcalculate,
		
		Monthcalc = Date.Month(Date.From(datetocalculate)),
		Yearcalc = Date.Year(Date.From(datetocalculate)),
		Monthcurr = Date.Month( datefromcalculateinternal),
		Yearcurr = Date.Year(datefromcalculateinternal),

		MonthDiff =(Monthcalc-Monthcurr), // -
		YearDiffinMonths = ((Yearcurr-Yearcalc)*12),
		Result = MonthDiff  - YearDiffinMonths
	in
		Result+1,

    AddDifferenceMonth = Table.AddColumn(ToDate, "BudgetMonth", each if [Include in financial forecast]="Yes" then [CurrentBudget]/(DifferenceMonth([Estimated Finish Date], [Estimated Start Date])) else 0),
	AddMonthTable = Table.AddColumn(
		AddDifferenceMonth,
		"MonthList",
		(add)=>  List.Transform({1..DifferenceMonth(add[Estimated Finish Date], add[Estimated Start Date])}, each Date.AddMonths(Date.StartOfMonth(add[Estimated Start Date]),_-1))),
    ExpandMonthRows = Table.ExpandListColumn(AddMonthTable, "MonthList"),
    ChangeType2 = Table.TransformColumnTypes(ExpandMonthRows,{{"MonthList", type date}})
in
	ChangeType2

[Jimmy801]

Hello @Anonymous 

you have to adapt the previous step.

so replace this piece of code

AddDifferenceMonth = Table.AddColumn(ToDate, "BudgetMonth"

with

AddDifferenceMonth = Table.AddColumn(#"Sorted Rows1", "BudgetMonth"

All the best

Jimmy

[Anonymous]

Thank you @Jimmy801 , this is very good!

However, I have two problems:

1. When I use the "MonthList" in the Axis and drill down, it doesn't seem to be able to distinguish between years:

The October, November, December budgets are from 2019.

I added a new project as a test:

When I instead use my "Calendar" Table to connect with the BudgetMonth, then it does not seem to register the new data I have inserted:

2. My second problem is that with the pivoting of the MonthList/BudgetMonth all "Projects" get multiplied up and scews the data. How can I make sure that each Project, CurrentBudget, etc. etc. only appears 1 time?

[Jimmy801]

Hello @Anonymous 

how do you make the connection between your calendartable and datatable? I can't see a way how it could connected as is seems text to me. In this case you need to add a new column to calculate the new format.

About your second question: 

you need to keep your original table as it is for you masterdata per project and create a new query referencing the original table and apply you monthly calculation. If there is the need, you can connect it afterwards to your original table.

Hope it helps

jimmy