Forum Discussion

Nekthen's avatar
Nekthen
Advocate I
10 years ago

Date Hierarchy in Matrix Visualisation

I have a simple list report in Excel with Invoice Date, Value, Analysis Code

Using the Invoice Date in the Column I can choose Date Hierarchy which shows Year, Quarter, Month and Day

As I am using a few years of information, Day is too great a level of detail. I cannot seem to get rid of it to show only Year, Quarter and Month

Am I missing something? 

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    You could create your own hierarchy with 3 custom columns:

     

    Year = YEAR([Date])

    Qtr = IF(MONTH([Date])<4,1,IF(MONTH([Date])<7,2,IF(MONTH([Date])<10,3,4)))

    Month = MONTH([Date])

     

    Get rid of your Date column from Rows and drag Year, Qtr and Month into the Rows area in that order.

    • fbrossard's avatar
      fbrossard
      Kudo Commander
      10 years ago

      you can also simplify the quarter formula by using this DAX expression

      Qtr = ROUNDDOWN(MONTH([Date])/4,0)+1