Forum Discussion

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

Calculating prorated value based on a condition

I have the below table. I need to calculate the annual contract based on the start date and end date of the contract.   As you can see from the above table, the first contract starts at 1 Ju...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • tex628's avatar
    tex628
    6 years ago

    Hello Kolumam,

    Take a look at the pbix attached to this reply, i hope it can provide some insight! 

    Br, 
    J

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Kolumam ,

 

I spent a lot of time on this, but your logic is too complicated, I really can't write a suitable formula, I hope super user can help you well.

 

Best Regards,

Dedmon Dai

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

    Hi Kolumam 

     I could provide a Power Query solution for that.

    If that's OK, please confirm how to handle partial end years: Shall they be considered as full years like the parital start years or taken pro rata?