Forum Discussion
Show selected and previous Quarter
I have a report with a slicer on Year and Quarter, which filters several visuals. Some of the visuals just need to show the selected Quarter, but some need to show the Current AND previous quarter. I'd like to be able to do without having to add another slicer. I've found the post below, and tried to copy this as it is almost the exact same requirement, but this doesn't seem to work for me. When I apply the filter i just don't get any results at all anymore in my table visual.
Solved: Show previous three quarters based on quarter sele... - Microsoft Fabric Community
Here's the code I'm using now based on the above.
Kwartaalfilter =
VAR _max =
SELECTEDVALUE ( Kwartaaltest[JaarKwartaal] )
VAR _min =
CALCULATE (
MAX ( 'Kwartaaltest'[JaarKwartaal] ),
FILTER (
ALL ( 'Kwartaaltest' ),
'Kwartaaltest'[Index]
= SELECTEDVALUE ( Kwartaaltest[Index] ) - 1
)
)
VAR _yearQuarter =
MAX ( 'Periode'[Jaar] ) * 100
+ MAX ( 'Periode'[Kwartaal] )
RETURN
IF ( _yearQuarter <= _max && _yearQuarter >= _min, 1, 0 )
Anybody have any idea where I'm going wrong, or even better: an easier way to accomplish this goal?
For anyone else that's trying to figure this out, I was able to accomplish my goal using this tutorial:
Show last 6 months based on user single slicer selection (youtube.com)
Just adjusted the formula slightly to show current and previous Quarter instead of last 6 months:
CurrentandPreviousQ = VAR PreviousDates = DATESBETWEEN(PreviousPeriode[Date], STARTOFQUARTER(PREVIOUSQUARTER(Periode[Date])),ENDOFQUARTER(Periode[Date])) Var Result = CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Periode' ), KEEPFILTERS (PreviousDates), USERELATIONSHIP ( 'PreviousPeriode'[Date], 'Periode'[Date] ) ) Return ResultWorks like a charm!
6 Replies
Hi mlvdijl ,
According to the expression, its final output needs to pass the filter Kwartaalfilter value of 1, please try to set the value to “is 1” via filter.If this does not solve the problem, please provide sample data and your expected results.
Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply Anonymous , the filter is set to 1 indeed, but not working.
Here's a simplified sample file
Card visual should show only results from the selected quarter. Table visual should show both selected and previous quarter.
Thanks again for looking into this!
Hi mlvdijl ,
So the problem arises in the relationship because when you filter the quarter, you will filter only the current quarter data and the rest of the data is filtered, so to achieve your goal, you need an additional unrelated field for filtering.
An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, I see what you mean in what the issue is but don't quite follow your solution. Looks like you've created an extra table as well as an extra measure? This might work in this simplified example, but the actual reports requires several measures and a calculation group actually, so I think it might become a bit too complicated then.
I'll let it go for now, and work around it in another way. Thanks anyway!
For anyone else that's trying to figure this out, I was able to accomplish my goal using this tutorial:
Show last 6 months based on user single slicer selection (youtube.com)
Just adjusted the formula slightly to show current and previous Quarter instead of last 6 months:
CurrentandPreviousQ = VAR PreviousDates = DATESBETWEEN(PreviousPeriode[Date], STARTOFQUARTER(PREVIOUSQUARTER(Periode[Date])),ENDOFQUARTER(Periode[Date])) Var Result = CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Periode' ), KEEPFILTERS (PreviousDates), USERELATIONSHIP ( 'PreviousPeriode'[Date], 'Periode'[Date] ) ) Return ResultWorks like a charm!
