cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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:
maxdatasalaris.jpg

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Calculate SUM where Date = MAX

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.

View solution in original post

3 REPLIES 3
Highlighted
Responsive Resident
Responsive Resident

Re: Calculate SUM where Date = MAX

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.

Highlighted
Solution Sage
Solution Sage

Re: Calculate SUM where Date = MAX

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.

View solution in original post

Highlighted
Helper V
Helper V

Re: Calculate SUM where Date = MAX

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors