Filter a column its already filtered by another column and have duplicate values in columns

[Anonymous]

Hi,

I am creating two column current year and previous year dynamically by selected year in the slicer.

for example if i have three year like 2019,2018,2017 or may be more, if user click 2019 it should show 2019 values in current year column and at the same time it should show previous year means 2018 details.

current year = IF(YEAR(FinalTable[Date]) = [Selected], VALUE(FinalTable[Value]), 0)  -- it is working.

 selected = 2019 by slicer selection

previous year = IF(YEAR(FinalTable[Date]) = [Selected]-1, VALUE(FinalTable[Value]),0)  -- it not working.

Even have duplicate values in that so i tried ALL,FirstBLANK, ALLEXCEPT but it is not showing correct value and some query gives erorr like table giving multiple value but accepting single value.

So can you please help me on this...

Thanks! 

[Anonymous]

Hi,

are you using FinalTable[Date] in the slicer? So, when a year is selected all other years are filtered out of the table? Is Value a column in the table? Are you creating measures? I suppose so since it's not possible to create dynamic columns.

However, I suggest something like this:

current year = CALCULATE(SUM(FinalTable[Value]), KEEPFILTERS(YEAR('FinalTable'[Date]) = [Selected])) 

previous year = CALCULATE([current year], PREVIOUSYEAR('FinalTable'[Date]) 

I also recommend moving the date to a separate date table.

[Anonymous]

Actually i am creating columns using above dax query. and i also tried by using mesures as you said but its giving error like A function calculate has been used in true/false expression that is used as table filter expression so not allowed.

[Anonymous]

Try

current year = CALCULATE(SUM(FinalTable[Value]), FILTER('FinalTable', YEAR('FinalTable'[Date]) = [Selected])) 

[Anonymous]

Hi @Anonymous , @Anonymous  , @Anonymous 

Columns can not be dynamic. Columns and calculated tables are get calculated at first load only.  You can not chnaged values based on slicer selection.

Measure are used for dynamic calculation.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Like @Anonymous suggested, a date dimension table would solve this issue. 

If you have a sample file that could be helpful.

[Anonymous]

Hello @Anonymous 

A variable is a great place to start here. You can even simplify your formula like this:

previous year = 
VAR pyear = [Selected] - 1
RETURN
CALCULATE( VALUE(FinalTable[Value]),0),
[Selected] = pyear)

[Anonymous]

but in the VALUE function i dont get FinalTable[Value] column and i even used VALUES but it gives error msg like multiple values getting accepting only single value