topic Re: Date Slicer Showing Previous Quarter in DAX Commands and Tips

Date Slicer Showing Previous Quarter

cn4422 — Tue, 17 Sep 2024 08:24:36 GMT

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! 😊

Re: Date Slicer Showing Previous Quarter

sjoerdvn — Tue, 17 Sep 2024 08:57:58 GMT

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.

Re: Date Slicer Showing Previous Quarter

cn4422 — Wed, 18 Sep 2024 08:12:20 GMT

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"?

Re: Date Slicer Showing Previous Quarter

sjoerdvn — Wed, 18 Sep 2024 12:45:41 GMT

Can't download the sample data (link is blocked) but the column(s) must be added to the existing date dimension table.

Re: Date Slicer Showing Previous Quarter

cn4422 — Thu, 19 Sep 2024 08:05:38 GMT

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?

Re: Date Slicer Showing Previous Quarter

Anonymous — Tue, 24 Sep 2024 08:32:13 GMT

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.

Re: Date Slicer Showing Previous Quarter

sjoerdvn — Tue, 24 Sep 2024 15:31:49 GMT

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)

Re: Date Slicer Showing Previous Quarter

cn4422 — Thu, 26 Sep 2024 09:37:12 GMT

Hi Anonymous ,

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

Re: Date Slicer Showing Previous Quarter

cn4422 — Thu, 26 Sep 2024 10:02:24 GMT

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