Showing last 4 quarters in Matrix visual (dynamically based on today's date)

[DotU]

Hi All,

I have a fact table with a date column and a calculated column which shows Quarter-Year.

I've created a Matrix which shows a simple SUM of the values column by the quarters. What I need to do is always show the Last 4 quarters only, excluding the Current Quarter.

I'm sure there is a good trick, but I've not been able to catch it yet, please help!!

PS the reason you don't see Current Quarter data in the image is because we don't have that data yet, we usually get it be the close of the 2nd month of the quarter.

Thanks in advance!

[SergioSilvaPT · Accepted solution]

Hi @DotU ,

I can give you a solution!

First you will need to create a calculated column that it's going to be a related quarter index:

Quarter Index (related) =

(YEAR(TODAY()) - YEAR('Calendar'[Date])) * 4 + QUARTER(TODAY()) - QUARTER('Calendar'[Date])

The bold 'Calendar'[Date]' you will have to replace for the column with date in your table, i think is RunDate.

Then you will get something similar to this:

And on the visual that you want to filter you have to add this new column as a filter and select only the values (1, 2, 3, 4) to have dynamically the last 4 quarters excluding the actual one.

like this:

i hope i solved your problem! 🙂

[DotU]

omg, the math behind this is sooo pretty! and yes it works.
If i show you guys what all i was starting to do, you will have a good laugh, lol
Thank God I stopped and asked!!
Thank you sooo much for your help!!!!!

[SergioSilvaPT]

No problem, I'm glad i could help!

Sometimes the easiest solution is the best!

[Anonymous]

,

It is possible but very long to explain.

Look at this video and everything will be clear.

https://www.youtube.com/watch?v=d8Rm7dwM6gc&t=883s