Date Slicer Showing Previous Quarter

[cn4422]

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:

Sample Data 

Thx for your help! 😊

[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)

[cn4422]

Hi @sjoerdvn !
Thanks for the clarification, that worked pretty well! 👍😀

[sjoerdvn]

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.

[cn4422]

Thanks 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.

[cn4422]

Thanks for your reply @sjoerdvn 

Strange that the download didn't work with google drive link (I have uploaded again, this time with a dropbox link - you can (usually) download the file just bei declining to register, in case you don't have a DP account).

Download Sample Data

I have here a screenshot from the date table and actually I don't know how to include the "relative quarter" into this... or did you mean to create a new dimension table with a relationship to the existing date table?

[Anonymous · Accepted solution]

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.

[cn4422]

Hi @Anonymous ,

thanks for your reply, I went with solution #2 and it worked just fine! 😉