Forum Discussion

RemiAnthonise's avatar
RemiAnthonise
Icon for Helper V rankHelper V
8 years ago
Solved

Calculate SUM where Date = MAX

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!

Need Help
Show and Tell
Tips and Tricks
  • rajulshah's avatar
    rajulshah
    8 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      rajulshah
      Icon for Resident Rockstar rankResident Rockstar
      8 years ago

      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.