Create a Calculated COlumn based on Slicer from a different table

[Anonymous]

I have created a SlicerTable with DateRange column having values  - YearToDate and Rolling12Months.

In my main Table I have 24 months of data and a datefield "ReportingDate" which I want to filter into Current year and Previous Year.

This means, when I select "YearToDate" in Slicer then I should have value as below

DateField           ClaculatedColumn

01/10/2020         CurrentYear

02/01/2020         CurrentYear

01/10/2019         PreviousYear

Can anyone suggest how can I achieve this. Thanks for the help/

02/01/2019         PreviousYear

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a sample to test:

Then create a slicer table:

Date SLicer = DISTINCT(SELECTCOLUMNS('Table',"Year",'Table'[DateField].[Year])) 

For your requirement, i think you can not use calculated column to achieve the result as you posted because calculated column can not be changed dynamically by selecting value in slicer.

So i advise using measure, please try this:

Measure = IF(YEAR(MAX('Table'[DateField]))=SELECTEDVALUE('Date SLicer'[Year]),"Current Year",IF(YEAR(MAX('Table'[DateField]))=SELECTEDVALUE('Date SLicer'[Year])-1,"Previous Year",BLANK()))

When you select one year in slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Greg_Deckler]

You would need a measure to do that I think. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490