Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Attached below is the PBIX if it helps.
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])
)
Attached below is the PBIX if it helps.
So is your example data provided below minus the AssignDate column and the AssignDate is what you want as the output?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |