Sales over the year with Dynamic year value

[Parihar1980]

Hello Friends , 

I am working on tablura model for a requrement where I have to show sales for Last 5 year in a chart. Now this Year value is coming from Year Slicer ; so when i clicked 2018 it should display sales for 2018, 2017,2016,2015 and 2014 respectively. and if i choose 2017 then till 2013 .

 Please suggest me some dynamic dax code where i can pass this value and get the resuly.

[v-juanli-msft]

Hi @Parihar1980

I'm afraid no direct feature can achieve this, Here is a workaround.

1. create a calendar table (table2) and manage relationship between two tables.

2.create measures in Table2, then add "year" column from Table 1 to the slicer.

Measure = SELECTEDVALUE(Table2[year])

Measure 2 = [Measure]-MAX('Table'[year])

Measure 3 = IF([Measure 2]>=0&&[Measure 2]<5,1,0)

3. add [measure3] to Visual Filter.

Finally, it works as your requirement

Best Regards

Maggie

[Parihar1980]

many thanks for your feedback. But I am using tabular model and I can't see this funcation while importing my model (SSAS) and using this i am creating my report. 

 Also SELECTEDVALUE funcation working in Direct query OR Import Mode not with tabular model

Any help or suggestion ?

[Ashish_Mathur]

Hi,

To keep things simple, why can you not just select 5 years instead of just 1?

[Parihar1980]

As per my requirement we have to show data based on selected Year value, so If User will select 2018 we have to diplay record from 2018,2017,2016,2015 and 2014. and As i menationed SELECTEDVALUE funcation is not working in tabular model. So I have to found some ways to display data based in user selection  

[Ashish_Mathur]

Hi,

I can try to solve it with the DATESBETWEEN() function.  Share the link from where i can download your PBI file.

[Parihar1980]

Many thanks Ashish for your kind word. But I can't share the data since its very sensitive and afcourse its assoicated with tabulader model even this is also road block. I am even agree to do this task using sql plus dax combination, I just want to do it since its users expectation.