Select Value as variable not working

[Anonymous]

Hello !

Im trying to change and old DAX that is not working anymore due to the year change, the last analyst didn´t considered year change.

So my ideia is to use SELECTEDVALUE as a variable on the formula, so it doesn´t need to be changed every year, heres the code:

Economia Real =

VAR A = [RS_TON 2019]

VAR B = [RS_TON 2020]

VAR C = [RS_TON 2021]

VAR D = [Peso NF 2019]

VAR E = [Peso NF 2020]

VAR F = [Peso Util 2021]

VAR G = [Frete 2020]

VAR H = [Frete 2021]

VAR I = [Volume 2020]

VAR J = [Volume 2021]

VAR YEAR = SELECTEDVALUE(Consolidado[Ano Faturamento])

RETURN

SWITCH(TRUE(),

YEAR = 2019, (A-A)*D,

YEAR = 2020, (B-A)*E)

the problem is, when i do filter 2020, for example, it doesnt actually calculate the SWITCH(TRUE() argument. It just simply summarize all my data (whichis sales values), and don´t execute the (B-A)*E) part of the formula, but the wierdest part, which i coudn´t figure out why, is that if i change VAR YEAR to = VAR YEAR = 2020, the code runs flawless, could i get any help ?

Wrong Value

correct value.

Thanks !

[V-pazhen-msft · Accepted solution]

@Anonymous 

I don't think you can just use selectedvalue() directly on the current table. Try following:

1. create a new table with year column, more years if needed.

2. You do not need VAR Year, remove it. And change the return formula:

Return

SWITCH(SELECTEDVALUE(NewTable[Year]),

2019, (A-A)*D,

2020, (B-A)*E)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks ! That actually worked 🙂

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak]

@Anonymous , Is this you actual formula or I doubt. Why so many var when you are not using them ?

This formula seem correct. Best way to test is

SWITCH(TRUE(),
YEAR = 2019, 1,
YEAR = 2020, 2

)

Check what value it returns

Post that return A , B and E one at time and check what is wrong. May be one formula have some issue

[Anonymous]

@amitchandak Hi!

yes, its my formula, i have this many variables just for now, i will try to fix them later, since i didn´t manage to make the first part of DAX to work, i plan to make them more easy to manipulate, most of these are year specifically formulas.

I´ve tried your advice but it didn´t seems to work 😞