Formula issue - SELECTEDVALUE

[Hung_Nguyen_889]

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

[Payeras_BI · Accepted solution]

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,

[v-yingjl · Accepted solution]

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.

[Payeras_BI · Accepted solution]

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,

[wdx223_Daniel]

maybe SELECTEDVALUE get a blank on those rows.

[Hung_Nguyen_889]

Thank you for your response,

SELECTEDVALUE got 2020 value. It still showed data from week 1 to week 5. 😞

[wdx223_Daniel]

try to chante your measure

RETURN _Previous_Year

[Payeras_BI]

Hi @Hung_Nguyen_889 ,

Try with this instead:

PY REVENUE =
CALCULATE ( [Total Revenue $], SAMEPERIODLASTYEAR ( DIM_DATE[date] ) )

Regards,

[Hung_Nguyen_889]

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