DAX Formula for Assign Date

[Anonymous]

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 that month

2)  starting with the second month, if the record had appeared in the previous month at least one time AND if it IS present in the first day of the second month, to retrieve as assign date - the first occurrence/date of the record in that month

OR

2) starting with the second month, if the record had appeared in the previous month at least one time AND if was NOT present in the first day of the second month, to retrieve as assign date for the second month - the first occurrence/date of the record in the second month

This process is being repeated for all months, by having in mind the first day of the month.

I have the below sample, with the AssignDate that needs to be retrieved.

If anything, please let me know.

Many thanks,

Melisa

record	Month	Date	AssignDate
1244333	March	3/5/2020	3/5/2020
1244333	March	3/15/2020	3/5/2020
1244333	March	3/25/2020	3/5/2020
1244333	April	4/1/2020	3/5/2020
1244333	April	4/5/2020	3/5/2020
1344222	March	3/3/2020	3/3/2020
1344222	March	3/9/2020	3/3/2020
1344222	April	4/5/2020	4/5/2020
1344222	May	5/1/2020	4/5/2020

[Anonymous]

Hi,

check out this support article from Microsoft, it can lead you somewhere near a solution.

https://docs.microsoft.com/en-us/dax/date-and-time-functions-dax

Regards,

Lewis

[Greg_Deckler]

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 · Accepted solution]

Attached below is the PBIX if it helps.

[Greg_Deckler]

Sorry, trying to paste that into the code windows was giving me some kind of wonky error.

[Greg_Deckler]

So is your example data provided below minus the AssignDate column and the AssignDate is what you want as the output?

[Anonymous]

@Greg_Deckler 

Yes, the last column is in fact the Output column.

Thank you,

Melisa