Calculating correct FTE

[JB_AT]

Good morning

I have a table with employee information. It shows a changes in employee data. I have a ValidFrom and ValidTo column, which indicates that something changed in their record.

It can happen that their FTE changes within a month timframe, example from 0,5 to 1.

I have a RunningTotal measure which technically works, but gives the wrong result if there is such a case like above. What I would like to achieve is to get the latest value. Ideally, this would change on the selected time frame, for example, if 2023 is selected in the date dlicer and all months are selected, then the latest value is shown, same for quarter, month etc...

Below is my current Running Total Measure. Which would show a combined value if there were multiple changes in a timeframe

FTE RT Test = 
/// Employees Amounts Over Time ///
VAR MaxDate =
    MAX ( DIM_Date[Date] )
RETURN
    CALCULATE (
        [Test FTE],
        KEEPFILTERS (
            'Emp History'[__VALID_FROM] <= MaxDate
                && 'Emp History'[__VALID_UNTIL] >= MaxDate
                && NOT ( CONTAINSSTRING ( 'Emp History'[JobProfil], "Trainee" ) )
        ),
        ALL ( DIM_Date ),
    USERELATIONSHIP(DIM_Date[Date], 'Emp History'[__VALID_FROM]))

 Thanks

[JB_AT · Accepted solution]

I adapted the [Test FTE] to show the last value and it works as expected

Test FTE = CALCULATE ( 
        LASTNONBLANK ( 'Emp History'[FTE], 1 ))