cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deermeat
Resolver I
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.

Deermeat_0-1651277260679.png

 

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

Sample File with Error: https://drive.google.com/file/d/1LFAZpJ_Z-pLKwJjRqo2VkC_sIgOXQLos/view?usp=sharing

Thank you for your help!

1 ACCEPTED SOLUTION
karnold
Resolver I
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!

View solution in original post

2 REPLIES 2
karnold
Resolver I
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!

Thank you! It work perfectly.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors