Need urgent help - DAX calculation

Question

I have the below table.&nbsp;Start Date of ContractEnd Date of ContractOperation NameComprehensive O&amp;M Price1/7/201931/8/2019X3751/9/201931/3/2020X40512/5/202031/12/2021X3851/4/201731/3/2018Y3801/4/201831/3/2020Y41011/5/202031/12/2021Y303&nbsp;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.&nbsp;Expected Output with Calculation:&nbsp;&nbsp;Operation NameYearAnnual ContractCalculationX2019395(375*60/180)+(405*120/180)X2020353.01(405*90/365)+(385*240/365)X2021385(385*365/365)Y2017380(380*365/365)Y2018396.97(380*90/365)+(410*270/365)Y2019410(410*365/365)Y2020300.23(410*90/365)*(303*240/365)Y2021303303*365/365&nbsp;Thanks in advance for the help.@amitchandak&nbsp;@parry2k&nbsp;@mahoney19&nbsp;@Amit&nbsp;@amitchandak&nbsp;@parry2k&nbsp;@az38&nbsp;@jdbuchanan71&nbsp;@mahoneypat&nbsp;@edhans&nbsp;@harshnathani&nbsp;@v-kellya-msft&nbsp;@MFelix&nbsp;@Ashish_Mathur&nbsp;@BA_Pete&nbsp;@ryan_mayu&nbsp;@kbuckvol&nbsp;@Alexander76877&nbsp;@Petazo&nbsp;@Mariusz&nbsp;@TomMartens&nbsp;@Greg_Deckler&nbsp;@tjd&nbsp;@Sean&nbsp;@mikstra&nbsp;@AllisonKennedy&nbsp;@EricHulshof&nbsp;@briandpeterson&nbsp;@USG_Phil&nbsp;@vpatel55&nbsp;@mwegener&nbsp;@v-piga-msft&nbsp;@tex628&nbsp;@sturlaws&nbsp;@Vvelarde&nbsp;@CheenuSing&nbsp;@MarcelBeug&nbsp;@Zubair_Muhammad&nbsp;@v-piga-msft&nbsp;@danextian&nbsp;@MattAL&nbsp;@MattAllington&nbsp;@roalexan&nbsp;@Alexander76877&nbsp;@kgc&nbsp;

greg_deckler · Answer

Kolumam&nbsp;- Can you explain the logic behind the calculations that you have presented?
&nbsp;
So, for example,&nbsp;
&nbsp;

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

&nbsp;
So, I get where the 375 and 405 come from. Is the 60 and 120 the number of months within 2019 for which the contract is valid and you are assuming 30 day months? Where does the 180 come from? I would think 60 + 120 but then the next line is:
&nbsp;
(405*90/365)+(385*240/365)
&nbsp;
And 90 and 240 do not add up to 365.&nbsp;
&nbsp;
Then you have the 2021 stuff where suddenly you switch to 365/365 (which is 1) soooooo.... Puzzled.
&nbsp;
All of that said, you are going to probably end up needing to use something like GENERATE since if you solve this in DAX because you need to essentially "invent" rows in a table and there are limited options for doing things like that.
&nbsp;

kolumam · Answer

Hi&nbsp;Greg_Deckler&nbsp;&nbsp;Please find my explanation for the calculation.&nbsp;(375*60/180)+(405*120/180)&nbsp;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.&nbsp;For this one:&nbsp;(405*90/365)+(385*240/365)&nbsp;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.&nbsp;For the last contract, the contract applies for whole year. Hence 385*365/365&nbsp;

mariusz · Answer

Hi&nbsp;Kolumam&nbsp;
&nbsp;
if you are looking to split the rate proportionally by days then consider the attached solutions using Power Query and DAX table
&nbsp;
Best Regards,MariuszIf this post helps, then please consider Accepting it as the solution.Please feel free to connect with me. LinkedIn 
&nbsp;

kolumam · Answer

Hi&nbsp;Mariusz&nbsp;&nbsp;Thanks for trying but your solution is incorrect. See below.&nbsp;For 2019, you are taking the whole year, rather you should only take the number of days between 1st July 2019 and 31st Dec 2019. So it will be (375*60/180)+(405*120/180) which is 395. As you can see for contract X, we have two contracts on the same year. If that happens, then you need to divide by the number of days from the start date of contract to end of the year of start date of contract. If there are no two contracts in a year, say for example, the start date is 1st April 2017 for contract Y, in that case for 2017, it should take the entire year. So it will be (380*365*365) which is 380 instead of 286.83. From 2018, the value is prorated accordingly.&nbsp;Do you get it?

kolumam · Answer

Hi people,&nbsp;&nbsp;My question is still not solved yet. Can anyone please help?@parry2k&nbsp;@mahoney19&nbsp;@Amit&nbsp;@parry2k&nbsp;@az38&nbsp;@jdbuchanan71&nbsp;@mahoneypat&nbsp;@edhans&nbsp;@harshnathani&nbsp;@v-kellya-msft&nbsp;@MFelix&nbsp;@Ashish_Mathur&nbsp;@BA_Pete&nbsp;@ryan_mayu&nbsp;@kbuckvol&nbsp;@Alexander76877&nbsp;@Petazo&nbsp;@Mariusz&nbsp;@TomMartens&nbsp;@Greg_Deckler&nbsp;@tjd&nbsp;@Sean&nbsp;@mikstra&nbsp;@AllisonKennedy&nbsp;@EricHulshof&nbsp;@briandpeterson&nbsp;@USG_Phil&nbsp;@vpatel55&nbsp;@mwegener&nbsp;@v-piga-msft&nbsp;@tex628&nbsp;@sturlaws&nbsp;@Vvelarde&nbsp;@CheenuSing&nbsp;@MarcelBeug&nbsp;@Zubair_Muhammad&nbsp;@v-piga-msft&nbsp;@danextian&nbsp;@MattAL&nbsp;@MattAllington&nbsp;@roalexan&nbsp;@Alexander76877&nbsp;@kgc&nbsp;

Forum Discussion

Need urgent help - DAX calculation

6 Replies

Related content

Urgent question about Policy update

Calculation on dax (Urgent)

Need Urgent Help on SamePeriodLastYear

urgent help needed

URGENT!! Convert Measure to Calculated Column

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

Operation Name	Year	Annual Contract	Calculation
X	2019	395	(37560/180)+(405120/180)
X	2020	353.01	(40590/365)+(385240/365)
X	2021	385	(385*365/365)
Y	2017	380	(380*365/365)
Y	2018	396.97	(38090/365)+(410270/365)
Y	2019	410	(410*365/365)
Y	2020	300.23	(41090/365)(303*240/365)
Y	2021	303	303*365/365