Most recent employee score value for each month

[Anonymous]

Hi All,

I have the a table in the below format. Its the socre card of each employee, however arrear data is also received in some months. 

As an example. For Emp ID 11111 the score received for month Jan was EE however, in the month of March an updated score for employee was received as ME, hence the latest score is ME.

Table 

Emp ID	Incentive Month	Score	Data received month
11111	Jan-20	EE	Feb-20
22222	Jan-20	ME	Feb-20
33333	Jan-20	NI	Feb-20
11111	Feb-20	EE	Mar-20
22222	Feb-20	ME	Mar-20
33333	Feb-20	NI	Mar-20
11111	Jan-20	ME	Mar-20
22222	Jan-20	EE	Mar-20

Desired output. 

	Incentrive Month
Emp ID	Jan-20	Feb-20
11111	ME	EE
22222	EE	ME
33333	NI	NI

Please help. 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

DateTable:

Here are the calculated columns and measure i created.

Columns:
Incentive Month = FORMAT('Table'[Incentive Date],"mmm-yy") 
Data received Month = FORMAT('Table'[Received Date],"mmm-yy")

Measure:
NewScore = 
var _currentemp = MAX('Table'[Emp ID])
var _currentincentivemonth = MAX('Table'[Incentive Month])
var _date = 
CALCULATE(
    MAX('Table'[Data received Month]),
    FILTER(
        'Table',
        'Table'[Emp ID] = _currentemp&&
        'Table'[Incentive Month]=_currentincentivemonth
    )
)

var _monthyear = FORMAT(_date,"mmm-yy")

return
LOOKUPVALUE('Table'[Score],'Table'[Incentive Month],_currentincentivemonth,'Table'[Emp ID],_currentemp,'Table'[Data received Month],_monthyear)

Finally, you may use the matrix visual to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Do you have Data received date also, or only formatted month? because if it a date or can be converted to date, We can use a Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedinformula like

calculate(max(table[Score]),
	filter(summarize(table,table[Emp ID],"_maxdt",max(table[Data received date]),"_maxGp",max(table[Emp ID])),
			table[Emp ID]=[_maxGp] && max(table[Data received date]=_maxdt)))

[Anonymous]

@amitchandak

Really appreciate you helping me on this.

Yes all dates are proper dates 1 Jan 2020 ,  Can you please provide details on where to put this formula. As in new Measure, or column or table.

[amitchandak]

This Should be a measure