topic Re: Retrive Status From other table using Date range in DAX Commands and Tips

Retrive Status From other table using Date range

TaufikMaggangka — Thu, 15 Sep 2022 02:10:48 GMT

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.

Re: Retrive Status From other table using Date range

tamerj1 — Wed, 14 Sep 2022 16:20:40 GMT

Hi TaufikMaggangka

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

Re: Retrive Status From other table using Date range

TaufikMaggangka — Wed, 14 Sep 2022 16:48:30 GMT

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.

Re: Retrive Status From other table using Date range

tamerj1 — Wed, 14 Sep 2022 17:51:14 GMT

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

Re: Retrive Status From other table using Date range

TaufikMaggangka — Thu, 15 Sep 2022 02:19:37 GMT

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.

Re: Retrive Status From other table using Date range

tamerj1 — Thu, 15 Sep 2022 06:03:24 GMT

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