Forum Discussion
Retrive Status From other table using Date range
Hi Everyone,
Really need help from expert to retrive status from other table using date parameter and ID. Here my case :
Table 1
| Id | Joining Date | Position |
| 1 | 1 January 2021 | Teacher |
| 1 | 1 January 2022 | Admin |
Table 2
| Id | Attendance Date |
| 1 | 2 February 2021 |
| 1 | 3 March 2021 |
| 1 | 2 July 2022 |
I want to retrive Position from table 1 to table 2, but using joining date(Table 1) and attendance date(Table 2) column as parameter, as you can see Id "1" have 2 position that different in 2021 and 2022.
I hope the result will be like this table:
| Id | Attendance Date | Position |
| 1 | 2 February 2021 | Teacher |
| 1 | 3 March 2021 | Teacher |
| 1 | 2 July 2022 | Admin |
As you can see the id on 2 july 2022 being differnet because there were new position from 1 january 2022, and the id took attendance in 2 july 2022.
Really need help from expert, already stuck with this for few days. Already tried with lookup value and firstnon blank function but not working.
Are the two tables linked via ID column? Are you looking for a measure or a calculated column?
TaufikMaggangka
Yes you are right, I did it the other way around which was wrong.Please refer to attached sample file with the solution
Position = VAR T1 = RELATEDTABLE ( 'Table 1' ) VAR T2 = FILTER ( T1, VAR CurrentAttendanceDate = 'Table 2'[Attendance Date] VAR CurrentJoiningDate = 'Table 1'[Joining Date] VAR T3 = FILTER ( T1, 'Table 1'[Joining Date] > CurrentJoiningDate ) VAR NextJoiningDate = COALESCE ( MAXX ( T3, 'Table 1'[Joining Date] ), TODAY ( ) ) RETURN CurrentAttendanceDate >= CurrentJoiningDate && CurrentAttendanceDate < NextJoiningDate ) RETURN MAXX ( T2, 'Table 1'[Position] )
5 Replies
Hi Anonymous yes, the table linked by ID columns and I need calculate column. I need to retrive position from table 1 to table 2. I write down the desire result on my case.
Really hope you can help, Already stuck.please try
Position =
VAR CurrentDate = Table2[Attendence Date]
VAR CurrentIDTable =
CALCULATETABLE ( Table2, ALLEXCEPT ( Table2, Table2[id] ) )
VAR TableAfter =
FILTER ( CurrentIDTable, Table2[Attendence Date] > CurrentDate )
VAR NextDate =
COALESCE ( MAXX ( CurrentIDTable, Table2[Attendence Date] ), TODAY () )
VAR T1 =
FILTER (
RELATEDTABLE ( Table1 ),
Table1[Joining Date] >= CurrentDate
&& Table1[Joining Date] < NextDate
)
RETURN
MAXX ( T1, Table1[Position] )
Are the two tables linked via ID column? Are you looking for a measure or a calculated column?
