undefined

[tejaswi08]

Hi , 

I am trying to achieve something and finding few difficulties in it. Any suggestions/help on it would help me a lot.

I have a date slicer(to select year) and a line chart showing metric value(month wise, having year in legend). Expectation is to have 2 years value (Selected & Previous year) from the year selected from the slicer. 

Eg; If I select 2017 in the slicer. Line chart must show value for 2016 & 2017 month wise.

I have basically calculated the metric value as below,

start_date = max(date)

end_date = FIRSTDATE( DATEADD( [date],-1,YEAR))  (OR) 

Value_2years = CALCULATE( SUM([Value]) , DATESBETWEEN( [Date] , [start_date] , [end_date] ) )

When I see the value alone or with some other dimension , it is working good. But when I see it along with date(which is in the slicer) only one year data is getting popuated.

The input from the slicer was taken to calculate start and end date on fly. But we dont need  that input to filter visual.

Is there any work around for this?

Thanks in Advance!

[Anonymous · Accepted solution]

Hi @tejaswi08,

In your scenario, firstly, create a Year table listing years(2011,2012,2013,2014,2015,2016,2017…), but don’t create any relationship between this Year table and your original table.

Secondly, in your original table(take sales table for example), create a year column using following DAX.

Year = YEAR(Sales[DateKey])

Then create a measure in your original table using DAX below.

CheckMeasure = IF(MAX(Sales[Year])>(MAX(Year[Year])-2) && MAX(Sales[Year])<=MAX(Year[Year]),1,0)

Thirdly, create a slicer using your Year table, and drag the CheckMeasure to the Visual level filters of your Line chart and filter the measure to 1 only. This way, when you select 2017 in the slicer, Line chart will show value for 2016 & 2017. You can review the following example for more details.

Thanks,
Lydia Zhang