locked
Why is my Calendar Table Calculated Column Blank when Result is Next Year? RRS feed

  • Question

  • Hi Everyone...

    *** I posted this in the wrong forum (Power Query) so I am posting again in Power Pivot as this is a DAX question. ***

    This may be a real easy answer but I have run across it in more than one table and I can't figure out why it's happening...

    I have a simple calendar table generated with the following:

    Calendar = 
        ADDCOLUMNS(
            CALENDAR(DATE(2020,1,1), DATE(2020,12,31)),
            "MoNo", MONTH([Date]),
            "YrMoNo", YEAR([Date]) * 100 + MONTH([Date]),
            "Month", FORMAT([Date],"MMMM"),
            "Year", YEAR([Date]),
            "Year-Month", FORMAT([Date], "YYYY-MMMM")
        )

    I then added a "Week End" calculated Column with the following code:

    Week End = DATEADD('Calendar'[Date], 7-WEEKDAY([Date],1),DAY)

    Everything works great for the Week End column except when the result is > 12/31/2020.  Here's a screen shot:

    Does anyone have any idea why this is happening?  If I add another column using this code:

    Week Ending Next Year = 
      IF(
        ISBLANK('Calendar'[Week End]), 
        DATE(2021,1,2),
        BLANK()
      )

    The missing dates are generated just fine.  See screen shot below:


    Seems strange to me.  What am I missing???

    Thanks!


    John Thomas

    Wednesday, August 12, 2020 8:33 PM

Answers

  • This behaviour is condusing the first time you hit it, but it's actually by design. The DATEADD function returns values from your date column that are "shifted" by the specified number or periods. (see https://docs.microsoft.com/en-us/dax/dateadd-function-dax) So if your date table does not have the date 1/2/2021 in it then DATEADD cannot return this date.

    But there is a simple workaround. You could fix your formula by doing a simple math operation on the date. eg.

    Week End = 'Calendar'[Date] + 7 - WEEKDAY([Date],1)


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by jbt_PwrPvt Thursday, August 13, 2020 6:13 PM
    Thursday, August 13, 2020 12:44 AM

All replies

  • This behaviour is condusing the first time you hit it, but it's actually by design. The DATEADD function returns values from your date column that are "shifted" by the specified number or periods. (see https://docs.microsoft.com/en-us/dax/dateadd-function-dax) So if your date table does not have the date 1/2/2021 in it then DATEADD cannot return this date.

    But there is a simple workaround. You could fix your formula by doing a simple math operation on the date. eg.

    Week End = 'Calendar'[Date] + 7 - WEEKDAY([Date],1)


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by jbt_PwrPvt Thursday, August 13, 2020 6:13 PM
    Thursday, August 13, 2020 12:44 AM
  • @Darren Gosbell 

    Thanks for the reply and the work around.  It worked out just fine and was similar to my work around.  I just missed that nuance about the DATEADD function.  I can't think of any off-hand, but I am sure returning BLANK() for dates that are not in the original dates column may come in handy in some situation...  probably a filtering situation.

    Thanks again...

    John


    John Thomas

    Thursday, August 13, 2020 6:17 PM