Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Sample File with Error: https://drive.google.com/file/d/1LFAZpJ_Z-pLKwJjRqo2VkC_sIgOXQLos/view?usp=sharing
Thank you for your help!
Solved! Go to Solution.
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!
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.