Forum Discussion

OlafK's avatar
OlafK
Frequent Visitor
5 years ago
Solved

Date Table with only quarter end dates

Hello,

 

I'm currently facing a problem I can't solve. I use a date table but I'm not able to generate a a date table showing only quarter end dates.  I managed to get one with only month end dates by using EOMONTH function. But I can't use ENDOFQUARTER together with addcolumns.

 

My date table is created by

 

Date =
ADDCOLUMNS (FILTER (
CALENDAR ( DATE(2019,11,30), TODAY()),
[Date] = EOMONTH([Date], 0 )),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

  

 

Thanks a lot

 

Olaf

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    OlafK , Try like

    "Qtr End", EOMONTH([Date],if(3-mod(month([Date]),3)=3,0,3-mod(month([Date]),3)))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    OlafK , Try like

    "Qtr End", EOMONTH([Date],if(3-mod(month([Date]),3)=3,0,3-mod(month([Date]),3)))

    • OlafK's avatar
      OlafK
      Frequent Visitor
      5 years ago

      Thanks a lot, even though my meaning was a bit different in showing only quarterend dates in the table, I just put your solution to another position and it worked quite well! 

       

      Date =
      ADDCOLUMNS (FILTER (
      CALENDAR ( DATE(2019,11,30), TODAY()),
      [Date] = EOMONTH([Date],if(3-mod(month([Date]),3)=3,0,3-mod(month([Date]),3)))),....