cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Prodigy

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

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.

2 REPLIES 2
Community Support

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

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.
Super User

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors