cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbiOP1
Frequent Visitor

Determine Yearly budget for multi-year contracts

Hello!

 

I have a table with contract data between the years 2013 and 2019. Each of the rows corresponds to a contract, with different columns listing its features, among which Date Start, Date End and Total Volume of Funds. All dates are dd/mm/yyyy.

My organisation wants to analyze how much available funding it has for any given year, based on how much funding from each of the contracts falls within that year. The calculation we want is not a really sophisticated one, so we are happy allocating the budget lineally, as if the funds would have to be spent regardless of the type of day (working/non-working). I understand I need to calculate the duration of each contract in days (done), then divide the total funding by the days to find the amount per day (done), to get the unit that I could then multiply by the number of days a given contract has in one year.

 

I am having trouble finding solutions for the last step online: calculating how many days each contract has within each specific year, so I can multiply that figure by the daily funds I have in a calculated column. This is something I could probably do in Python, but my knowledge of DAX is even more limited and I cannot seem to figure out how to solve this.

 

Below, a mock-up of the table I am working with (columns "Contract Code" to "ContractEnd"), and an example of how I want my output to look like (columns "Budget2015" to "Budget2018"). Note the table should adjust the available funds for each year according to each contract´s start- and end dates:

 

  
ContractCodeAmountContractStartContractEndBudget2015Budget2016Budget2017Budget2018
Code1   100.000,00 €01/01/201631/12/2017                  -   €   50.000,00 €   50.000,00 €                  -   €
Code2   200.000,00 €01/01/201531/12/2018   50.000,00 €   50.000,00 €   50.000,00 €   50.000,00 €
Code3   300.000,00 €01/06/201631/12/2018   67.762,46 €   77.412,51 €   77.412,51 €   77.412,51 €

 

Any help with this would be really appreciated!

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi, @pbiOP1

You could try to refer to this post:

https://community.powerbi.com/t5/Desktop/Track-the-Workload-by-counting-the-number-of-in-progress-ta...

 

In your case, just add a column that divide Total Volume of Funds by total days for each contract.

 

Best Regards,

Lin

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Johanno
Responsive Resident
Responsive Resident

Hi, I think sample data and expected output would be really helpful so that others understand the question easier.
pbiOP1
Frequent Visitor

Hello Johanno,

 

Thanks for the suggestion! I have added a sample of the table I am working with (it is a mock-up, though, as I cannot use the real data).

 

Hope it works!

Johanno
Responsive Resident
Responsive Resident

Hi, I'm not proud of this solution, there should be more sophisticated ones. But I guess you can create a calculated column per year with:

Budget 2015 = 
VAR TotalDays = Table1[ContractEnd]-Table1[ContractStart]
VAR StartDate = DATE(2015;01;01)
VAR EndDate = Date(2015;12;31)
VAR ActualStartDate = IF(Table1[ContractStart]<StartDate;StartDate;Table1[ContractStart])
VAR ActualEndDate = IF(Table1[ContractEnd]>EndDate;EndDate;Table1[ContractEnd])
RETURN Table1[Amount]*IF(ActualEndDate-ActualStartDate+1<0;0;ActualEndDate-ActualStartDate+1)/TotalDays

Note that 2016 is i leap year.

 

Then you get (I guess there's a typo in your last row):

Capture.JPG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!