DAX CALCULATE Using MAX Error

[SimonNU]

Hi Guys

I'm trying to sum up all values from the current period. I assumed the below would work:

=CALCULATE(sum('UTILIZATION SHEETS'[CS]),'UTILIZATION SHEETS'[Period]=max('UTILIZATION SHEETS'[Period]))

But I receive:

"Calculation error in measure 'UTILIZATION SHEETS'[Measure 1]: A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Typing in the latest period, e.g. 11 instead of "max('UTILIZATION SHEETS'[Period])", works but this obviously isn't ideal.

Does anyone have a solution?


Thanks!

 

[SimonNU]

perhaps a massive case statement i.e.


select case max('UTILIZATION SHEETS'[Period])


case 1
=CALCULATE(sum('UTILIZATION SHEETS'[CS]),'UTILIZATION SHEETS'[Period]=1)
case 2
=CALCULATE(sum('UTILIZATION SHEETS'[CS]),'UTILIZATION SHEETS'[Period]=2)
case 3
=CALCULATE(sum('UTILIZATION SHEETS'[CS]),'UTILIZATION SHEETS'[Period]=3)
case 4
=CALCULATE(sum('UTILIZATION SHEETS'[CS]),'UTILIZATION SHEETS'[Period]=4)
case 5
=CALCULATE(sum('UTILIZATION SHEETS'[CS]),'UTILIZATION SHEETS'[Period]=5)


etc...

 

[scottsen]

Your first instinct was correct -- though, you can probably also use VALUES('UTILIZATION SHEETS'[Period]).

When you see "____ has been used in a True/False expression that is used as a table filter expression. This is not allowed." It always means "I did something complicated in my call to calculate, I need to use FILTER() instead".

=CALCULATE(sum('UTILIZATION SHEETS'[CS]), FILTER('UTILIZATION SHEETS', 'UTILIZATION SHEETS'[Period]=max('UTILIZATION SHEETS'[Period])))

And you should be good.