Hi,

You should do the PRORATA() first and then aggregate with the correct BY to the level you want.

I’d have said Sub team but as there is no change here it won’t impact much.

I also suggest following our standard app for this, which takes care of all the staging logic into metrics you can easily use

Hi Cedric,

It’s quite interesting that you’re experiencing a side effect of using LASTNONBLANK in a case where you’d actually rather have the LAST value.

If you calculate the Dataload.EntryDaterBY LASTNONBLANK: Dalataload.Employee] and Dataload.ExitDatexBY LASTNONBLANK: Dalataload.Employee] separately (e.g. in a Formula Playground), you’ll see they will both show 31/01/2024 - of which the PRORATA is indeed 0.

If you instead use Dataload.EntryDateEBY LAST: Dalataload.Employee and Dataload.ExitDateaBY LAST: Dalataload.Employee, it will give you resp. 31/01/2024 and blank (empty value) - which is what you could use in your Prorata - though it will only display values for the last Job that employee is taking.

Another alternative would be using Dataload.EntryDateaBY MIN: Dalataload.Employee] for the start date, which should give you the minimal date in the transaction for that employee, which could work - though that won’t work with MAX modifier on the term date because that would ignore the BLANK value. 

One option to work around that specific point could be to add a value far in the future e.g. IFBLANK(Dataload.ExitDate, DATEVALUE("2030-12-31", "yyyy-MM-dd")) which would fill a value even though the exit date is blank. You can either add that in a calculated property of your transaction and reference it, or directly use it in the PRORATA formula, though it might make things less legible.

PRORATA(
    Month,
    IFBLANK(Dataload.EntryDate, DATEVALUE("2010-01-01", "yyyy-MM-dd"))-BY MIN: Dataload.Employee],
    IFBLANK(Dataload.ExitDate, DATEVALUE("2030-12-31", "yyyy-MM-dd"))-BY MAX: Dataload.Employee]
)

Thanks you for the detailed answer.
Option 2 is working like a charm.