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

     

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!