Slicer on a Measure

[Anonymous]

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.

[v-jayw-msft]

Hi @Anonymous ,

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

[Anonymous]

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.

[v-jayw-msft]

Hi @Anonymous ,

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

[Ashish_Mathur]

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

[Anonymous]

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