PowerBI - salary historic

[Anonymous]

Hello everyone

I have a problem that I am not able to solve, I need to show the employee's salary in a pipeline, but the salary adjustments only occur in some periods of the year. but it is necessary to show me the most recent to date of the filter.


In the month less than April 04/19 the value must be 2,674.08 until 07/01/17, then 2,633.04 until 08/17 etc.

https://drive.google.com/drive/folders/1LbKVnWinpoOjT8Fi6KTSqOPdyi62Iywk?usp=sharing

[Anonymous · Accepted solution]

Hi Fábio, 

I fail to see why the should be 2,674.08 until 07/01/17, there is an entry in the table at 1/4/2018 were the value is 2,633.04.

Also there are duplicate dates in the history table. Is that really the case, and if so, how do you want to deal with it. 

If it is not the case the following measure should give you your result:

r1 = 
var CurrentPeriod = max('Dcalendario'[Date])
return 
sumx(topn(1, filter('Historico_salary', 'Historico_salary'[DTMUDANCA]<CurrentPeriod), 'Historico_salary'[DTMUDANCA].[Date],DESC ), 'Historico_salary'[SALARIO])

Jan 

[Anonymous]

Thank you,

In the original data model I have the date and time in the column, so I will always return the most recent one.

Your measure met perfectly. Thanks.