Calculating correct FTE

JB_AT — Tue, 19 Dec 2023 07:22:32 GMT

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

Re: Calculating correct FTE

JB_AT — Tue, 19 Dec 2023 08:17:39 GMT

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

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

topic Calculating correct FTE in DAX Commands and Tips

Calculating correct FTE

Re: Calculating correct FTE