I have a requirement to bill for the isoweeks of occupation in the previous month. The weeks are tiered such that...
(tier1) week1 = 23
(tier2) week2 = 28
(tier3) week3 = 35
(tier4) week4+ = 46
I have provided a sample of the table below:
The Weeks column counts the number of weeks in the previous month from TODAY() [2022-11-29]. Also note that 1 isoweek is subtracted from the total should occupation rollover from month to month to avoid duplicated billing from before the previous month-this has already been accounted for in the Weeks column, I also account for the current tier in the Tier column (I'm hoping that these helpers assist with final result)
If we look at Id 1, this entity has a total occupation of 4 isoweeks (2022-09-21 to 2022-10-21) however the intention is to bill 2 isoweeks in month 10. This entity is currently in tier 4. The result should be 81 (35+46).
If we look at Id 4, this entity has a total occupation of 13 isoweeks (2022-08-09 to 2022-10-31... it's still in occupation as at the end of month 10) however the intention is to bill 5 isoweeks in month 10. This entity is currently in tier 4. The result should be 230 (46+46+46+46+46).
If we look at Id 6, this entity has a total occupation of 3 isoweeks (2022-10-05 to 2022-10-19) the intention here is to bill 3 isoweeks in month 10. This entity is currently in tier 3. The result should be 86 (23+28+35).
I hope this makes sense. I greatly appreciate any and all help!
EDIT: I have attempted SUMPRODUCT using an array but either I am doing it incorrectly or the function isn't designed to work for my issue.
