Retrive Status From other table using Date range

[TaufikMaggangka]

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.

[tamerj1 · Accepted solution]

Hi @TaufikMaggangka 

Are the two tables linked via ID column? Are you looking for a measure or a calculated column?

[TaufikMaggangka]

Hi @Tamer 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.

[tamerj1]

Hi @TaufikMaggangka 

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] )

[TaufikMaggangka]

Thank you for trying to help @tamerj1 , the result is not my expetations.

I try this on PBIX file here

If using the sample case on the PBIX file, I hope the result will be like this :

ID	Attendance Date	Position
1	2 February 2021	Teacher
1	3 March 2022	Admin
2	2 February 2021	Teacher

Really need your future help.

[tamerj1 · Accepted solution]

@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] )

[tamerj1 · Accepted solution]

Hi @TaufikMaggangka 

Are the two tables linked via ID column? Are you looking for a measure or a calculated column?