cancel
Showing results for
Did you mean:
Resolver I

## A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter exp

Hello,

I'm trying to find averages for data that is 3 months ago from the current date chosen in the slicer. I have created this measure to solve this issue: I'm only matching Month and Year, as each month will only have 1 date.

``````Score average =
CALCULATE(
AVERAGE( Sheet1[Score] ),
ALL( Sheet1[Score] ),
YEAR(Sheet1[Date]) = YEAR([3monthsago]),
MONTH(Sheet1[Date]) = MONTH([3monthsago])
)``````

And [3monthsago] is: Can safely ignore the -10, it is just to ensure that the right month is chosen.

``3monthsago = DATE(YEAR([CurrentDate]), MONTH([CurrentDate]) - 3,DAY([CurrentDate] -10))``

And [CurrentDate] is just the quick measure of concatenated list of values of the Date used in the slicer, so that I can identify which Date is being chosen in the slicer:

``````CurrentDate =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sheet1'[Date])
VAR __MAX_VALUES_TO_SHOW = 999
RETURN
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
__MAX_VALUES_TO_SHOW,
VALUES('Sheet1'[Date]),
'Sheet1'[Date],
ASC
),
'Sheet1'[Date],
", ",
'Sheet1'[Date],
ASC
),
", etc."
),
CONCATENATEX(
VALUES('Sheet1'[Date]),
'Sheet1'[Date],
", ",
'Sheet1'[Date],
ASC
)
)``````

However, when I try to use Score average, I get the error: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

This error seems to be because of the fact that [3monthsago] is a measure. Is there anyway to fix this?

1 ACCEPTED SOLUTION
Resolver I

You can use a VAR in place of the measure:

``````Score average =
VAR _3MonYear = YEAR([3monthsago]
VAR _3MonMonth = MONTH([3monthsago]
RETURN
CALCULATE(
AVERAGE( Sheet1[Score] ),
ALL( Sheet1[Score] ),
YEAR(Sheet1[Date]) = _3MonYear,
MONTH(Sheet1[Date]) = _3MonMonth
)``````

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

2 REPLIES 2
Resolver I

You can use a VAR in place of the measure:

``````Score average =
VAR _3MonYear = YEAR([3monthsago]
VAR _3MonMonth = MONTH([3monthsago]
RETURN
CALCULATE(
AVERAGE( Sheet1[Score] ),
ALL( Sheet1[Score] ),
YEAR(Sheet1[Date]) = _3MonYear,
MONTH(Sheet1[Date]) = _3MonMonth
)``````

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Resolver I

Thank you! It work perfectly.

Announcements