Forum Discussion

DotU's avatar
DotU
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • SergioSilvaPT's avatar
    SergioSilvaPT
    4 years ago

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

     

SergioSilvaPT's avatar
SergioSilvaPT
Icon for Resolver V rankResolver V
4 years ago

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's avatar
    DotU
    Frequent Visitor
    4 years ago

    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's avatar
      SergioSilvaPT
      Icon for Resolver V rankResolver V
      4 years ago

      No problem, I'm glad i could help!

       

      Sometimes the easiest solution is the best!