cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Parihar1980 Regular Visitor
Regular Visitor

Sales over the year with Dynamic year value

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.

 

Trend Report.JPG

6 REPLIES 6
Community Support Team
Community Support Team

Re: Sales over the year with Dynamic year value

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.

10.png

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)

11.png

 

3. add [measure3] to Visual Filter.

12.png

 

Finally, it works as your requirement

13.png14.png

 

 

Best Regards

Maggie

Parihar1980 Regular Visitor
Regular Visitor

Re: Sales over the year with Dynamic year value

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 ?

Super User
Super User

Re: Sales over the year with Dynamic year value

Hi,

 

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

Parihar1980 Regular Visitor
Regular Visitor

Re: Sales over the year with Dynamic year value

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  

Super User
Super User

Re: Sales over the year with Dynamic year value

Hi,

 

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

Parihar1980 Regular Visitor
Regular Visitor

Re: Sales over the year with Dynamic year value

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. 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 47 members 1,097 guests
Please welcome our newest community members: