Forum Discussion
Helper IICompare two tables for rate increase
Thanks, this works great. Appreciate your help.
Helper IIHi,
They could appear in any order unfortunately due to issues with the source data. I could add an index column maybe to then do a count on job ref and return 1 for new job and zero if job exists previously on another row.
Then the bonus would only appear against a record with a 1 in in it?
Community SupportHi Trebor84
You ware right, use the index to identify the first rows.
First, add an index column in power query editor and use the following expression will get the result:
Column =
VAR _first =
MINX(
FILTER(
'Table 1',
[Employee ID] = EARLIER( 'Table 1'[Employee ID] )
&& [Job Ref] = EARLIER( 'Table 1'[Job Ref] )
),
[Index]
)
VAR _nolimit =
IF(
RELATED( 'Table 2'[Increse End Date] ) = BLANK(),
DATE( 2099, 1, 1 ),
RELATED( 'Table 2'[Increse End Date] )
)
RETURN
IF(
[Index] = _first,
IF(
RELATED( 'Table 2'[Increase Start Date] ) <= [Date]
&& _nolimit >= [Date],
RELATED( 'Table 2'[Bonus] ),
BLANK()
),
BLANK()
)
result:
I put the pbix file in the end you can refer
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Trebor84
Thanks, this works great. Appreciate your help.