Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

'DATEADD' Expects a contiguous selection

Hello, I'm trying to create a measure where I calculate revenue recognized on a sales order in specific timeframes (see below table).  I'm using two measures calculating revenue then % of revenue ag...
Need Help
lbendlin's avatar
lbendlin
Super User
6 years ago

General guidance is that your data model should contain a calendar/dates table that covers your fact date range (plus a year before) and that has contiguous dates. You'll also want to mark the table as a Date table to avoid wasting model memory.

 

Then run the DATEADD() against the dates table.

 

If you don't want to do that you can use EDATE() for month math. It doesn't care about contiguity (sp?)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks lbendlin , both fact tables do have their own date tables and are marked as such. DAX is the same for both date tables, the only thing I notice is I don't have a prior year but I can add it. I'll try the EDATE() function.   

     

     

    Date_Recognized =
    VAR Days = CALENDAR ( DATE ( 2016, 1, 1 ), TODAY() )
    RETURN ADDCOLUMNS (
    Days,
    "DR Year", YEAR ( [Date] ),
    "DR Month Number", MONTH ( [Date] ),
    "DR Month", FORMAT ( [Date], "mmmm" ),
    "DR Year Month Number", YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1,
    "DR Month Year", FORMAT ( [Date], "mmm-yyyy" ),
    "DR Year Month", FORMAT ( [Date], "yyyy-mm" ),
    "DR Week Number", WEEKNUM([Date], 2),
    "DR Year Week Number", (FORMAT ( [Date], "yyyy" )) & "-" & WEEKNUM([Date], 2),
    "DR Week Start", FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 1), "DD-MMM-YY"),
    "DR Week End", FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 7), "DD-MMM-YY"),
    "DR Week Name", (FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 1), "DD-MMM-YY")) & " to " & (FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 7), "DD-MMM-YY")),
    "DR Prev Week Number", (((([Date] - WEEKDAY ( [Date], 2 ) + 1)) - TODAY()) /7),
    "DR Working Day", IF(WEEKDAY([Date], 2) <6, 1, 0),
    "DR Day Short", FORMAT ( [Date], "ddd" ),
    "DR Day Long", FORMAT ( [Date], "dddd" ),
    "DR prev Month Number", (YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1) - (YEAR ( TODAY() ) * 12 + MONTH ( TODAY() ) - 1),
    "DR Current Week", FORMAT ((TODAY() - WEEKDAY ( TODAY(), 2 ) + 1), "DD-MMM-YY"),
    "DR Current Month Number", MONTH ( TODAY() ),
    "DR Current Year", YEAR ( TODAY() )
    )
  • kevnotec's avatar
    kevnotec
    Advocate I
    2 years ago

    EDATE, yes that's the one!

    Just want an equivalent of DATEADD in SQL, albeit will only work for months, but that's just what I need and without all the fuss.