Error: 'CALCULATE' has been used in a True/False expression

Question

I try to create a dynamic measure that uses a date slicer as input. I want the price of the MIN date that is selected with a date slicer as the benchmark for calculating the price development.

Here my input table:

Date__________Price______Performance
13/03/2008______5________0.00%
14/03/2008______5________0.00%
18/03/2008______5________0.00%
19/03/2008______5________0.00%
20/03/2008______5________0.00%
25/03/2008______5.001____0.02%
26/03/2008______5.054____1.08%
27/03/2008______5.036____0.72%
28/03/2008______5.046____0.92%
31/03/2008______5.047____0.94%
01/04/2008______5.022____0.44%

Scenario:
So in this scenario now, the date slicer bottom border is 13/03/2008 and would use 5 as the benchmark price for the calculation of the performance accordingly.

However, when I change the date slicer bottom border to 26/03/2008 I want 5.054 as the new benchmark and accordingly, the performance should show 0.00% in this row and all further rows down the line should use the price from 26/03/2008 as the benchmark and calculate their performance accordingly.

The final goal I have is to create a dynamic line chart visualization that shows the performance development from the bottom border onward.

DynamicPerformance :=
VAR vbenchmark = 
    CALCULATE(
        SELECTEDVALUE('Input Data'[Price]), 
        'Input Data'[NAV_Date] = 
            CALCULATE(
                min('Input Data'[NAV_Date]), 
                ALLSELECTED('Input Data')
            )
    )
RETURN
AVERAGE('Input Data'[Price]) / vbenchmark

However, I keep getting the following error message:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.)

Any help is very much appreciated! MLU

Thanks @AlexisOlson for pointing that out. I edited the question. I am stuck with an error message that I keep receiving, but I do not understand how to fix it. — MLU

Alexis Olson Alexis Olson · Accepted Answer · 2020-03-16T21:15:15

The CALCULATE function only allows for simple True/False expressions to be used as the filter argument. You cannot put another CALCULATE expression there.

One simple fix is to pre-calculate that date beforehand and store it as a variable:

DynamicPerformance :=
VAR vdate =
    CALCULATE ( MIN ( 'Input Data'[NAV_Date] ), ALLSELECTED ( 'Input Data' ) )
VAR vbenchmark =
    CALCULATE (
        SELECTEDVALUE ( 'Input Data'[Price] ),
        'Input Data'[NAV_Date] = vdate
    )
RETURN
    AVERAGE ( 'Input Data'[Price] ) / vbenchmark

Error: 'CALCULATE' has been used in a True/False expression

1 Answers