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 rec...
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.

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.

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

    Thankks rajulshah ! I'm happy with the result.