Time intelligence related issue

[Anonymous]

Hello Community,

Greetings!

I am running into some serious issue, I have a data set which has columns shipstart, shipend and profit amount, 
1) Based on this data set I wnat to calculate if date is in between 1/8/2022 and 31/7/2023 I want sum of Profit amount this is easy part.
2) Tricky part is, if my shipend date is after 31/7/2023, First I have to calculate number of months between shipstart and shipend date than divide profit amount by total month .
3) Calculate total months between shipstart date and 31/7/2023 than multiply it with profit amount which I got from point 2.
4) The total should show combination of all 3 points.

Ship start	Ship end	Approved offers $			Total months from shipstart to 7/31/2023	Total number of months	Profit amount/Total number of month	H8*F8
8/1/2022 0:00	8/31/2022 0:00	$22,476	$22,476
8/1/2022 0:00	9/1/2022 0:00	$3,892	$3,892
8/1/2022 0:00	9/29/2022 0:00	$2,788	$2,788
8/1/2022 0:00	12/31/2022 0:00	$22,653	$22,653
8/1/2022 0:00	3/1/2023 0:00	$18,488	$18,488
8/1/2022 0:00	7/31/2023 0:00	$205,340	$205,340
8/1/2022 0:00	9/1/2023 0:00	$3,644	$3,123.43		12	14	$260.29	$3,123.43
8/2/2022 0:00	3/31/2022 0:00	$22,668	$22,668
8/2/2022 0:00	12/30/2022 0:00	$31,805	$31,805
8/2/2022 0:00	12/31/2022 0:00	$16,899	$16,899

I hope you got my question and help me to resolve this issue.

Thanks in advance for your help and time!

[Anonymous]

Thanks realy appreciate your help @Greg_Deckler 

[Greg_Deckler · Accepted solution]

@Anonymous Maybe:

Column = 
  VAR __ShipStart = [Ship start]
  VAR __ShipEndDate = [Ship end]
  VAR __Offer = [Approved offers $]
  VAR __TotalMonths = DATEDIFF( __ShipStart, __ShipEnd, MONTH ) + 1
  VAR __TotsMonths = DATEDIFF( __ShipStart, DATE( 2023, 7, 31 ) + 1
  VAR __Result = DIVIDE( __Offer, __TotalMonths ) * __TotsMonths
RETURN
  __Result