How to filter data based on parameter

[parry2k]

Hello,

Can someone help me on following:

My dataset contains date, product, gross margin, revenue and cost of sales and I want a report to list all the products based on user input below:

Let's we are looking at data by month:

1. Gross margin increased from previous period <some value>

2. And Revenue is decreased from previous period by <some value>

3. Cost of sales is increated from previous period by <some value>

How this can be achieved? I don't see any parameters etc in BI whereas other tools like Tableau allows to create filters based on parameters. I'm sure it is doable but not sure what is the best way to achieve this. 

Thanks in advance,

P

[leonardmurphy · Accepted solution]

First, you'd want to create measures, in DAX, to represent the percentage increase/decrease. (Which you'd break down into 3 measures ideally: a measure for current month gross margin and a measure for previous month gross margin, then once you have those, the measure to calculate the growth is straightforward).

Once you have measures representing the growth, then you can add filters based on the value (or you can create KPIs to identify if the growth is above/below your target threshold).

It's not automatic, but can be very powerful (as DAX can handle custom calendars for example - such as YTD growth when your fiscal year starts in April rather than January)

This is an old article, but a good introduction to time intelligence in DAX (while it talks about year-over-year growth, the same concepts can be adapted to work for months or other time periods): http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx