Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Formula for Assign Date

Hi,   I would like to retrieve the assign date by following the below conditions: 1)  for the first month of the record, to retrieve as assign date - the first occurrence/date of the record in tha...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Create this column:

    MonthNum = MONTH([Date])

    and then this column:

    AssignDate =
    VAR __FirstMonth = MINX(FILTER('Data','Data'[record] = EARLIER('Data'[record])),[MonthNum])
    VAR __FirstDate = MINX(FILTER('Data','Data'[record] = EARLIER('Data'[record]) && [MonthNum] = __FirstMonth),[Date])
    VAR __CurrentMonthDate = MINX(FILTER('Data','Data'[record] = EARLIER('Data'[record]) && 'Data'[MonthNum] = EARLIER('Data'[MonthNum])),'Data'[Date])
    VAR __WhoseOnFirst = IF(__CurrentMonthDate = DATE(YEAR(__CurrentMonthDate),MONTH(__CurrentMonthDate),1),TRUE(),FALSE())
    RETURN
    SWITCH(TRUE(),
    [MonthNum] = __FirstMonth,__FirstDate,
    __WhoseOnFirst && __FirstMonth = [MonthNum] - 1,__FirstDate,
    MINX(FILTER('Data','Data'[record] = EARLIER('Data'[record]) && [MonthNum] <> __FirstMonth),[Date])
    )

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago
      Sorry, trying to paste that into the code windows was giving me some kind of wonky error.