Need help on calculating annual contract (DAX) [Stuck for Days]

[Kolumam]

Hi All,

I have the below table.

Start Date of Contract	End Date of Contract	Operation Name	Comprehensive O&M Price
1/7/2019	31/8/2019	X	375
1/9/2019	31/3/2020	X	405
12/5/2020	31/12/2021	X	385
1/4/2017	31/3/2018	Y	380
1/4/2018	31/3/2020	Y	410
11/5/2020	31/12/2021	Y	303

Based on the start date of contract and end date of contract for each Operation Name, I need to calculate the annual contract for each operation name.

Expected Output with Calculation: 

Operation Name	Year	Annual Contract	Calculation
X	2019	395	(375*60/180)+(405*120/180)
X	2020	353.01	(405*90/365)+(385*240/365)
X	2021	385	(385*365/365)
Y	2017	380	(380*365/365)
Y	2018	396.97	(380*90/365)+(410*270/365)
Y	2019	410	(410*365/365)
Y	2020	300.23	(410*90/365)*(303*240/365)
Y	2021	303	303*365/365

Please find my explanation for the calculation.

(375*60/180)+(405*120/180)

Here 60 is the number of days for which the contract is valid (end date - start date) and 180 is the total number of days between 1 July 2019 and 31st December 2019. I am using the days approximately but ideally it should be the exact number of days.

For this one: (405*90/365)+(385*240/365)

90 because the contract is from 1st Jan 2020 to 31 March 2020 and 240 is because the the start date is 12/5/2020 and ends at 31st Dec 2020. I am taking rough numbers. Not the exact difference in days.

For the last contract, the contract applies for whole year. Hence 385*365/365

Thanks in advance for the help.

 @parry2k @mahoney19  @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft @tex628 @sturlaws @Vvelarde @CheenuSing @MarcelBeug @Zubair_Muhammad @v-piga-msft @danextian @MattAL @MattAllington @roalexan @Alexander76877 @kgc 

[v-lili6-msft]

hi  @Kolumam 

You could refer to this same blog:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

The only difference is why denominator is 180 or 360 in your case.

Regards,

Lin

[lbendlin]

That is a true nightmare. I would go down to the day level and for each operation and day calculate the day's contract value.