Forum Discussion
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 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 |
Attached below is the PBIX if it helps.
6 Replies
So is your example data provided below minus the AssignDate column and the AssignDate is what you want as the output?
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
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])
)- Sorry, trying to paste that into the code windows was giving me some kind of wonky error.