cancel
Showing results for
Did you mean:
Helper I

## Slicer on a Measure

Hi,

I have data like

Name       Sales       Quarter

A               100          2020Q1

A               250          2020Q2

B                50            2020Q1

B                 75           2020Q2

I want to have a quarter to quarter filter on the data so only that data is displayed where the Q on Q is > my given number

So in case i give quarter on quarter as say greater than 25, the rows for the column B should not be displayed

Only onw row should be displayed as

emp  qoq_Sales

A       150

since i won't be having quarter displayed in the final table.

I have created a measure which calculates sum of the current quarter and subtracts the sum of the previous quarter. I only need the difference between the current quarter and the previous quarter and that is already done using the time intelligence function.

I can see the quarter on quarter value in my table, it's just filtering it doesn't seem to work for me.

5 REPLIES 5
Community Support

Hi @rajat_1284 ,

Check the formula.

``````Measure =
var _year = LEFT(SELECTEDVALUE('Table'[Quarter]),4)
var _month = RIGHT(SELECTEDVALUE('Table'[Quarter]),1)
var _previous = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])&&LEFT('Table'[Quarter],4)=_year&&RIGHT('Table'[Quarter],1)+0=_month-1))
var _diff = IF(ISBLANK(_previous),0,SUM('Table'[Sales])-_previous)
return
_diff``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Helper I

25 is just an idea. I want the user to select any value, and that is why i wanted a slicer.  I tried the whatifparameter, it works but since my value is -100 billion to + 100 billion, the interval needs to be higher in the generate series.

What i wanted effectively is, that based on the measure, which is a calculation of Current quarter - previous quarter

i wanted all calculation values to be saved in a separate table, and then i could find out the min and the max of those values and get myself a slicer based on it. It would help me in reducing the series limitations.

There should be a better way for this.

Community Support

Hi @rajat_1284 ,

If you want a visual, you could modify the measure as below.

``````Measure =
var _year = LEFT(SELECTEDVALUE('Table'[Quarter]),4)
var _month = RIGHT(SELECTEDVALUE('Table'[Quarter]),1)
var _previous = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])&&LEFT('Table'[Quarter],4)=_year&&RIGHT('Table'[Quarter],1)+0=_month-1))
var _diff = IF(ISBLANK(_previous),0,SUM('Table'[Sales])-_previous)
return
if(_diff>selectedvalue(slicer),1,0)``````

Then add the measure to visual filter value =1.

If you want a table, I'm affraid it's not supported to create a table by selected value in slicer.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User III

You should be able to filter the quarterly difference measure by >25.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

It is just for example, it has to be through a slider option or a user input.

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.