Measure that returns the current value

[rosscortb]

Hello,

Hoping you can help.

I have two tables, headcount and Submitted Approved.

I have used this measure to bring in the sector that the employees works in from the headcount table to the Submitted Approved table. I haven't used lookup as gives the error of to many values when one is expected.

Sector = CALCULATE(FIRSTNONBLANK(Headcount[Sector],1),

FILTER(Headcount,Headcount[SAP] = SubApproved[SAP ID]

))

This works fine, however, it seems to return the wrong value if an employee has changed sector at some point.

In the example below using the measure above these two employees are showing as 

SAP ID        Sector

1848321     Corp Mgt( should be Finance)

2638713     Corp Mgt

If I swap firstnonblank to lastnonblank if get

SAP ID        Sector

1848321     Finance(correct)

2638713     Thermal(should be Corp Mgt)

Any ideas?

Thanks Ross

[v-diye-msft]

Hi @rosscortb 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[MFelix]

HI @rosscortb ,

If you have a date field in both table you should use it to make an additional filter on your values so that you can have the correct result at the correct time.

Can you share some sample data?

[rosscortb]

Hi, I do have have two dates fields in the submitted and approved which is basically start and end date for annual leave so don't think its relevant

[MFelix]

Hi @rosscortb ,

If you don't have any start and end date how do you know the Sector of a specificic employee at a certain time?

Can you share a sample data and setup of the relationships in you model?