Yearly calculation based on start date and end date

[Kolumam]

I have a table with start date and end date for each contract and I would like to calculate the annual price for each contract on a prorated basis. Please find the sample table and expected output.

Sample Table:

Contract Name	Start Date of Contract	End Date of Contract	Price
X	1/3/2017	31/3/2018	387
X	1/4/2018	31/3/2019	425

Expected Output:

Year	Contract Name	Pro-rated Price
2017	X	290.25
2018	X	415.5
2019	X	293.75

@amitchandak @parry2k @mahoneypat @harshnathani @lbendlin @AntrikshSharma @AlB @AllisonKennedy @BA_Pete @Mariusz @az38 @Rocco_Emmaneel @ibarrau @Ashish_Mathur 

[Ashish_Mathur]

Hi,

I think your answers for 2018 and 2019 are incorrect.  You may download my PBI file from here.

Hope this helps.

[Kolumam]

Thanks @Ashish_Mathur  It seems to be correct now but I have one another condition. 

If the start date starts inbetween a year for example April 2017, then the price is the same as the original price. That means if the price is 387 from April 2017 to May 2018, then the price for year 2017 is 387.

[Ashish_Mathur]

You are welcome.  Show a clear example and show your expected result (without any mistake please).

[Kolumam]

Hi @Ashish_Mathur 

Please find my table and expected output:

Year	Annual Contract Price
2017	380
2018	402.50
2019	410

Here is the explanation:

2017 = 380 since the start date starts in the middle of the year. 

2018 = 380 * (end date - start date)/365 + 410 * (end date - start date)/365

similarly for other years.....

See in the above table, the first entry is 380 instead of 285 since the number of days in the calendar year in 2017 is < 365 for the first entry (start date of contract). Similarly for the calculation of the rest of the year, I want to use the difference in days, rather than a difference in months since the difference in days is more accurate.

Please let me know if you still need any clarification.

[Ashish_Mathur]

Hi,

I cannot understand this logic.  Why is the price of the first contract not being pro-rated for 9 months only?

[Kolumam]

Hi @Ashish_Mathur 

For the first year alone, it is not prorated as the contract is defined for a year. So the price for the first year will be the same as the price.

[Kolumam]

Hi @Ashish_Mathur any update?

[Mariusz]

Hi @Kolumam 

See if this post can help:

https://community.powerbi.com/t5/Desktop/Distribute-Revenue-by-periods-having-start-date-and-end-dat...

https://community.powerbi.com/t5/Desktop/Distribute-Revenue-across-months-based-upon-days-in-the-mon...

https://community.powerbi.com/t5/Desktop/Distribute-Projected-Revenue-Annually/m-p/261754#M117008

also, see the attached example.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[BA_Pete]

Hi @Kolumam ,

Can you provide the calculation for your [Pro-rated Price] column, or explain exactly what you want it to show please?

Thanks,

Pete

[Kolumam]

Hi @BA_Pete 

Basically Prorated price is calculated as follows:

Prorated Price = Contract Price * (end date month - start date month)/12. See calculations below for each year.

2017 - 387 * 9/12

2018 - 387 * 3/12 + 425 * 9/12

2019 - 425 * 3/12

[amitchandak]

@Kolumam , check for measure 1 in the file. In 2019 we have only 3 months of data so showing only 3 months. There are few other overlaps too

https://www.dropbox.com/s/c2fgkaq17webog7/price%20split%20beteen%20start%20and%20end%20date.pbix?dl=...

[Kolumam]

@amitchandak I tried but see below the values are wrong.

380 * 9/12 = 285 should be the value for 2017 but as per your measure, it is giving 310. Please let me know.

[amitchandak]

@Kolumam , you have data from march 1-march, which means 10 months not 9

[Kolumam]

@amitchandak  No see screenshot below, my data starts from 1st April 2017. Please help.