Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Kolumam - Can you explain the logic behind the calculations that you have presented?

 

So, for example, 

 

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

 

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:

 

(405*90/365)+(385*240/365)

 

And 90 and 240 do not add up to 365. 

 

Then you have the 2021 stuff where suddenly you switch to 365/365 (which is 1) soooooo.... Puzzled.

 

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.

 

  • Kolumam's avatar
    Kolumam
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Hi Greg_Deckler 

     

    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

     

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Kolumam 

       

      if you are looking to split the rate proportionally by days then consider the attached solutions using Power Query and DAX table

       

      Best Regards,
      Mariusz

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

      Please feel free to connect with me.
      LinkedIn

       

      • Kolumam's avatar
        Kolumam
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        Hi Mariusz 

         

        Thanks for trying but your solution is incorrect. See below.

         

        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. 

        Do you get it?