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.