Calculate SUM where Date = MAX

[RemiAnthonise]

Hi guys,

I couldn't find the right answer, maybe you can help me.

I've got a table, see image below. It's possible that salary, bonus etc gets updated so I want to calculate with the most recent record of it. In this case, with the MAX ValidFrom.

For example, I want to calculate the salary:

Salaris = [Factor opslag] * CALCULATE(SUM(WorkerPositionEarningCodes[Amount]); WorkerPositionEarningCodes[EarningCodeId] = "Salaris" && MAX(WorkerPositionEarningCodes[ValidFrom])) All text in my formula written in black is allright. So the red part gives me the following error:

"A function ‘CALCULATE’ has been used in a true-false expression that is used as a table filter expression. This is not allowed."

How can I solve this? Or this isn't the right way?

Thanks in advance!

[Anonymous]

You miss something, after the max.

you should reference the fgact table (code), like:

&& 'FactTable'[ID] = MAX('DimTable'[ID])

I hope I'm seeing things clearly here.

[rajulshah · Accepted solution]

Hello @RemiAnthonise,

This can be restructured as:

Salaris = [Factor opslag] * CALCULATE(SUM(WorkerPositionEarningCodes[Amount]); WorkerPositionEarningCodes[EarningCodeId] = "Salaris"; FILTER(WorkerPositionEarningCodes,WorkerPositionEarningCodes[ValidFrom]=MAX(WorkerPositionEarningCodes[ValidFrom])))

Hope this works.

Regards.

[RemiAnthonise]

Thankks @rajulshah ! I'm happy with the result.