Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Date Hierarchy matrix

Hello,

I have an equation for date:

Dates =
ADDCOLUMNS(
CALENDAR( DATE ( 2013, 1, 1), DATE ( YEAR ( TODAY() ), 12, 31) ),
"Month Year", FORMAT ( [Date], "mmm-yyyy" ),
"MonthYearSort", YEAR ( [Date] ) * 100 + MONTH ( [Date] )
)
I have a date hierarchy:
 

 

When I arrange these in Matrix viz. I am able to see days only till April.

I have data until August. How do I make the table show the data till August? 

Need Help
Tips and Tricks

7 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    I believe you are using both system calendar (Auto Date Time ) and your Calendar. IF so, use only the calendar that you have created. There you can add YEAR, Month name and so on, then try.

    If you need a complete calendar use this:

    Calendar = 
	ADDCOLUMNS(
		CALENDARAUTO() /*CALENDAR(date(startyear,1,1),date(endyear,12,31))*/
		,"Day Name",FORMAT([Date],"DDDD")
		,"Day of Week",(WEEKDAY([Date],1))
		,"Day of Month",DAY([Date]) 
		,"Week",DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))-(WEEKDAY([Date],1)-1)
		,"Week Name", "Week of " & DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))-(WEEKDAY([Date],1)-1)
		,"Week of Year", WEEKNUM([Date],1)
		,"Month", DATE(YEAR([Date]),MONTH([Date]),1)
		,"Month Name", FORMAT([Date],"MMMM")
		,"Month of Year", MONTH([Date])
		,"Month Year Name", FORMAT([Date],"MMM") & " " & YEAR([Date])
		,"Month Year Name Sort",(100*YEAR([Date])+MONTH([Date]))
		,"Quarter",DATE(YEAR([Date]),SWITCH(ROUNDUP(DIVIDE(MONTH([Date]),3,1),0),1,1,2,4,3,7,4,10),1)
		,"Quarter Name", "Q" & ROUNDUP(MONTH([Date])/3,0)
		,"Quarter Year Name", "Q" & ROUNDUP(MONTH([Date])/3,0) & " " & YEAR([Date])
		,"Year", DATE(YEAR([Date]),1,1)
		,"Year #",YEAR([Date])
	)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your reply. How do I figure out if I am using my date column or an autogenerated column?

      I am using the below columns:

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Fowmy ,

      Thank you for the reply. What changes should I make to the above code to start the year calculation from 2013?

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        Anonymous 

        I set it from 2013 to 2020. If you need it, you can change it to any date. 


        Calendar = 
	ADDCOLUMNS(
		CALENDAR(date(2013,1,1),date(2020,12,31)),
		,"Day Name",FORMAT([Date],"DDDD")
		,"Day of Week",(WEEKDAY([Date],1))
		,"Day of Month",DAY([Date]) 
		,"Week",DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))-(WEEKDAY([Date],1)-1)
		,"Week Name", "Week of " & DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))-(WEEKDAY([Date],1)-1)
		,"Week of Year", WEEKNUM([Date],1)
		,"Month", DATE(YEAR([Date]),MONTH([Date]),1)
		,"Month Name", FORMAT([Date],"MMMM")
		,"Month of Year", MONTH([Date])
		,"Month Year Name", FORMAT([Date],"MMM") & " " & YEAR([Date])
		,"Month Year Name Sort",(100*YEAR([Date])+MONTH([Date]))
		,"Quarter",DATE(YEAR([Date]),SWITCH(ROUNDUP(DIVIDE(MONTH([Date]),3,1),0),1,1,2,4,3,7,4,10),1)
		,"Quarter Name", "Q" & ROUNDUP(MONTH([Date])/3,0)
		,"Quarter Year Name", "Q" & ROUNDUP(MONTH([Date])/3,0) & " " & YEAR([Date])
		,"Year", DATE(YEAR([Date]),1,1)
		,"Year #",YEAR([Date])
	)

         

        ________________________

        Did I answer your question? Mark this post as a solution, this will help others!.

        Click on the Thumbs-Up icon if you like this reply 🙂

        YouTube, LinkedIn