Count of FTE with internal mobility

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.EntryDate[BY LASTNONBLANK: Dalataload.Employee] and Dataload.ExitDate[BY 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.EntryDate[BY LAST: Dalataload.Employee and Dataload.ExitDate[BY 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.EntryDate[BY 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]
)