Hi Giuseppe -

This is an interesting question. So I was able to create the following table.

There are two scenarios, that come to mind, that you have to account for:

1) what do you do with ongoing subscriptions

2) what about subscriptions that are multi year

So here is one possibility: (change all field names to match your DB)

let sYear := year(Start); <--gets the year from the Start date field
let eYear := year(End); <--gets the year from the End date field
if eYear = null then<--if eYear is null then this is an ongoing subscription
     let curYear := year(today());<--gathers the current year
     if curYear = sYear then<--checks to see if current year matches the start year
        let xMon := month(today()) - month(Start) + 1;<--simple equation, add 1 so it includes the first month
        MonthlyFee * xMon<--amount paid so far
     else<--this is when the current year is greater than start year
        let xYear := year(today()) - sYear;<--first we figure out how many years have passed
        let xMon := 12 * xYear + month(today()) - month(Start) + 1 ;<--here we figure out how many months have passes
        MonthlyFee * xMon<--now we figure out how much we have paid
     end
else<--this is when there is an end date
     if sYear = eYear then<--check to see if the end Year and start Year are in the same year
        let xMon := month(End) - month(Start) + 1;
        MonthlyFee * xMon
     else<--this is what happens when they are not
        let xYear := eYear - sYear;
        let xMon := 12 * xYear + month(End) - month(Start) + 1;
        MonthlyFee * xMon
     end
end

I hope this gives you an idea on how to approach it. Let us know how it goes.

Hi Guiseppe -

Most things are possible. I'll give just one example. Here is what I've come up with:

If we take our previous code and put it in a new field, which I call Exp2020, and we will try to figure out how much a service costed you in 2020 you can do:

let sYear := year(Start);
let eYear := year(End);
let checkS2020 := if sYear <= 2020 then 1 end;<--checks to see if a service started in 2020 and assigns a 1
let checkE2020 := if eYear >= 2020 or eYear = null then<--checks to see if a service ended in 2020 or is still ongoing then assigns a 1
1
end;
let check2020 := checkS2020 + checkE2020;<--adds the two variables above, so we only want the follow formula to run when this variable equals 2.
switch check2020 do<--we add a switch command so we don't have to add another if statement
case 2:<--we only provide a formula when the variable equals 2, so the formula has been simplified
   if sYear = 2020 then<--if the service started in 2020
     let xMon := 12 - month(Start) + 1;
     MonthlyFee * xMon
   else<--the service has been running for the whole year
     MonthlyFee * 12
   end
end