Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |