Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
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
You should be able to filter the quarterly difference measure by >25.
It is just for example, it has to be through a slider option or a user input.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |