Forum Discussion
Slicer overriding visual level filter (cannot use edit interactions)
I need to display voluntary turnover data - current quarter and last quarter - in the same table visual. I also have a Quarter slicer so that you can filter the visual by each quarter. When Q2 is selected, I need the column 'Voluntary Turnover Previous Quarter' to show Q1 values.
Here is the measure I'm currently using that returns the previous quarter's data - the problem is it's still affected by the Quarter slicer and only shows the values if Q1 is selected (I need it show values for Q1 when Q2 is selected). For additional context, PreviousQuarterDate is a calculated column that only returns dates if they are in the previous quarter. 'Date' is a universal date table that has a Quarter column that is used for the Quarter slicer. This Date table has a 1-many relationship with the VolTurnoverData table column 'Date'
Hi,finley0726Hello, amitchandak ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
I am glad to help you.According to your description, you want to display the data of the previous and current quarter of a particular quarter in the date table after the slicer selects the quarter, not only the data of the quarter currently selected by the slicer
If my understanding is correct, you can refer to my test below
I created two metrics that calculate the data for the previous and current quarter of the quarter
and only the previous quarter's data (calculating the data that fits within this timeframe and marking it as 1)
Here are the results of my tests
What is important is that: I did not create a relationship between the calendar table “time” and the data table “Sales_Table” as a slicer filter field, because that would have resulted in the direct filtering of the field that you are experiencing.
I use the result of the decision to create the measure value to filter the data, which acts as a slicer filter (at this point, the slicer only serves to provide the filter value, and has no actual filtering effect, since there is no relationship between the tables).
At this point in the visual will only display M_Current&prior results for the value of 1, the advantage of doing so is that you can set your own needs according to the scope of screening, such as filtering the last quarter and the quarter's data or only filtering the last quarter's data.
Here is the DAX code:
M_Current&prior = VAR maxDate=MAX('Time'[Date]) //maxDate shows the last day of the selected quarter. VAR minDate=EOMONTH(maxDate,-6)+1 //minDate shows the first day six months before maxDate, which is the first day of the previous quarter VAR result= IF(SELECTEDVALUE('Sales_Table'[Date])>=minDate&&SELECTEDVALUE(Sales_Table[Date])<=maxDate, 1,0) RETURN resultM_prior quarter = VAR maxDate=MAX('Time'[Date]) //maxDate shows the last day of the selected quarter. VAR minDate=EOMONTH(maxDate,-6)+1 //minDate shows the first day six months before maxDate, which is the first day of the previous quarter VAR minDateEnd=EOMONTH(maxDate,-3) VAR result= IF(SELECTEDVALUE('Sales_Table'[Date])>=minDate&&SELECTEDVALUE(Sales_Table[Date])<=minDateEnd, 1,0) RETURN resultI hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
finley0726 , Use measures like below
based on selected date
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))based on today
Last Qtr Today =
var _today = today()
var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
var _min = eomonth(_max,-3)+1
return
CALCULATE([Net], FILTER(all('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max))Hi,finley0726Hello, amitchandak ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
I am glad to help you.According to your description, you want to display the data of the previous and current quarter of a particular quarter in the date table after the slicer selects the quarter, not only the data of the quarter currently selected by the slicer
If my understanding is correct, you can refer to my test below
I created two metrics that calculate the data for the previous and current quarter of the quarter
and only the previous quarter's data (calculating the data that fits within this timeframe and marking it as 1)
Here are the results of my tests
What is important is that: I did not create a relationship between the calendar table “time” and the data table “Sales_Table” as a slicer filter field, because that would have resulted in the direct filtering of the field that you are experiencing.
I use the result of the decision to create the measure value to filter the data, which acts as a slicer filter (at this point, the slicer only serves to provide the filter value, and has no actual filtering effect, since there is no relationship between the tables).
At this point in the visual will only display M_Current&prior results for the value of 1, the advantage of doing so is that you can set your own needs according to the scope of screening, such as filtering the last quarter and the quarter's data or only filtering the last quarter's data.
Here is the DAX code:
M_Current&prior = VAR maxDate=MAX('Time'[Date]) //maxDate shows the last day of the selected quarter. VAR minDate=EOMONTH(maxDate,-6)+1 //minDate shows the first day six months before maxDate, which is the first day of the previous quarter VAR result= IF(SELECTEDVALUE('Sales_Table'[Date])>=minDate&&SELECTEDVALUE(Sales_Table[Date])<=maxDate, 1,0) RETURN resultM_prior quarter = VAR maxDate=MAX('Time'[Date]) //maxDate shows the last day of the selected quarter. VAR minDate=EOMONTH(maxDate,-6)+1 //minDate shows the first day six months before maxDate, which is the first day of the previous quarter VAR minDateEnd=EOMONTH(maxDate,-3) VAR result= IF(SELECTEDVALUE('Sales_Table'[Date])>=minDate&&SELECTEDVALUE(Sales_Table[Date])<=minDateEnd, 1,0) RETURN resultI hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
