Forum Discussion
Formula issue - SELECTEDVALUE
Hi guys,
I have issue when using the SELECTEDVALUE.
If I hard-code year number ( 2020 ), the data of "PY Revenue" shows in all weeks. (first picture).
And it's vice versa, I replace 2020 value by " SELECTEDVALUE(DIM_DATE[fin_year])-1 ", the data just show to Week 5 (second picture).
I tried in another way, but I got and error. (3th picture)
Please help me to understand and how to fix this case.
Thank you very much
Hi Hung_Nguyen_889 ,
It is hard to tell without checking your pbix file (consider sharing a sample if you still cannot sort this out) but, to rule out a possible cause, make sure the filter you have placed in your Filter Pane (fin_year = 2021) comes also from the DIM_DATE table.
I tried your formula with some dummy data and works fine when filtering as said before. See below:
Regards,
Hi Hung_Nguyen_889 ,
As the error message describes, SELECTEDVALUE() could not be used as a T/F expression with a filter expression in the formula, you can use a variable to store it and use it in CALCULATE() function as @ Payeras_BI mentioned.
In addition, you can also add a FILTER() function in the formula to quote SELECTEDVALUE() directly which is allowed in DAX:
PY Revenue = CALCULATE ( [Total Revenue $], FILTER ( ALL ( 'DIM_DATE' ), DIM_DATE[fin_year] = SELECTEDVALUE ( DIM_DATE[fin_year] ) - 1 ) )Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Hung_Nguyen_889 ,
It is hard to tell without checking your pbix file (consider sharing a sample if you still cannot sort this out) but, to rule out a possible cause, make sure the filter you have placed in your Filter Pane (fin_year = 2021) comes also from the DIM_DATE table.
I tried your formula with some dummy data and works fine when filtering as said before. See below:
Regards,
Hi Hung_Nguyen_889 ,
Try with this instead:
PY REVENUE = CALCULATE ( [Total Revenue $], SAMEPERIODLASTYEAR ( DIM_DATE[date] ) )Regards,
Hi Payeras_BI
Thank you for your response,
I can't use SAMEPERIODLASTYEAR function. Because, our financial date is different.
The fist date of 2020 is starting at '2019-12-29' and the ending date is '2021-01-02'
The fist date of 2021 is starting at '2021-01-03' and the ending date is '2022-01-01'
So, it is incorrect when I use SAMEPERIODLASTYEAR
maybe SELECTEDVALUE get a blank on those rows.
Thank you for your response,
SELECTEDVALUE got 2020 value. It still showed data from week 1 to week 5. 😞
try to chante your measure
RETURN _Previous_Year
Hi Hung_Nguyen_889 ,
As the error message describes, SELECTEDVALUE() could not be used as a T/F expression with a filter expression in the formula, you can use a variable to store it and use it in CALCULATE() function as @ Payeras_BI mentioned.
In addition, you can also add a FILTER() function in the formula to quote SELECTEDVALUE() directly which is allowed in DAX:
PY Revenue = CALCULATE ( [Total Revenue $], FILTER ( ALL ( 'DIM_DATE' ), DIM_DATE[fin_year] = SELECTEDVALUE ( DIM_DATE[fin_year] ) - 1 ) )Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
