Forum Discussion
Helper VDate Slicer Showing Previous Quarter
Hi,
I would like to create a dynamic slicer that automatically is set to and shows the previous quarter.
I have a date table but I'm not quite sure how to accomplish this. Probably with a DAX formula?
Here is the link to the example data:
Thx for your help! 😊
Thanks for the reply from sjoerdvn , please allow me to provide another insight:
Hi, cn4422
I offer you two options to achieve this need:
1.First, use the quarter and year of the auto-date as the slicer, as shown in the following image:
The downside is that you need to manually go to the click.
2.Second, use the following measure:
MEASURE = IF ( QUARTER ( MAX ( 'Datum'[Date] ) ) = QUARTER ( TODAY () ) - 1 && YEAR ( MAX ( 'Datum'[Date] ) ) = YEAR ( TODAY () ), 1 )And apply it to the visualization:
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
sjoerdvnSolution Sage
I usually add "relative" calculated columns to the date dimension table. So "relative quarter" would be 1 for next quarter, 0 for the current , -1 for the previous and -2 for the quarter before the previous etc. Likewise for year, month, week and day. This way you can also easily report on the last 3 months.
cn4422Thanks for your reply!
I'm not 100% sure how to do this...
Should I add a new Column into the date table (the one from the sample data) - or do I need to create a new dimension table where I put the "relative quarter"?
- sjoerdvn
Can't download the sample data (link is blocked) but the column(s) must be added to the existing date dimension table.
- sjoerdvn
In Table view, select Table Tools and "New Column" Then add the definition below
Relative quarter = VAR rq = QUARTER([date]) VAR tq = QUARTER(TODAY()) VAR ry = YEAR([date]) VAR ty = YEAR(TODAY()) RETURN rq - tq + 4 * (ry - ty)