Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DotU
Frequent Visitor

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

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.

 

DotU_1-1652373300832.pngDotU_2-1652373408537.png

 

 

 

Thanks in advance!

1 ACCEPTED SOLUTION
SergioSilvaPT
Resolver V
Resolver V

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:
 
SergioSilvaPT_0-1652375796848.png

 

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:

SergioSilvaPT_1-1652375933287.png

 

i hope i solved your problem! 🙂

 

Regards,
Sérgio Silva

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

View solution in original post

4 REPLIES 4
SergioSilvaPT
Resolver V
Resolver V

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:
 
SergioSilvaPT_0-1652375796848.png

 

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:

SergioSilvaPT_1-1652375933287.png

 

i hope i solved your problem! 🙂

 

Regards,
Sérgio Silva

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

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

No problem, I'm glad i could help!

 

Sometimes the easiest solution is the best!

Regards,
Sérgio Silva

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/
Anonymous
Not applicable

,

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.