Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Table 1 | |||
Name | Date in Service | First Project | Second Project |
Alex K | 1-1-2018 | I want here the first project of Table 2 of Alex K based on project start date vs Date In service | Here the second |
Jay kay | 1-2-2018 | ||
Bernd leo | 1-3-2018 | ||
Table 2 | |||
Name | name Project | Project Start Date | Project End Date |
Alex K | AA technology | 2-1-2018 | 14-2-2018 |
Alex K | AB technology | 15-2-2018 | 11-3-2018 |
Alex K | AC technology | 12-3-2018 | 14-2-2018 |
Jay kay | AA technology | 15-2-2018 | 19-2-2018 |
Jay kay | AB technology | 20-2-2018 | 11-3-2018 |
Jay kay | AC technology | 12-3-2018 | 14-4-2018 |
Jay kay | AD technology | 15-4-2018 | 1-3-2018 |
Bernd leo | AA technology | 2-3-2018 | 11-3-2018 |
Bernd leo | AB technology | 12-3-2018 | 14-3-2018 |
Bernd leo | AC technology | 15-3-2018 |
i have 2 tables and in table one i want to have the first and scond project of table 2 based on the nearest Project start date in comparison with the date in service
example: Alex K first project is AA technology because 2-1-2018 is the first nearest date tot the date in service 1-1-2018
kind regards
Solved! Go to Solution.
Thank so much!
I also need the start date of the first and second project.
Can you help me with it?
@Anonymous
Try with these.
Date First Project = CALCULATE ( MIN ( Table2[Project Start Date] ), FILTER ( CALCULATETABLE ( VALUES ( Table2 ) ), Table2[Project Start Date] >= Table1[Date in Service] ) )
Date Second Project = CALCULATE ( MIN ( Table2[Project Start Date] ), FILTER ( CALCULATETABLE ( VALUES ( Table2 ) ), Table2[Project Start Date] >= Table1[Date in Service] ), Table2[name Project] <> FirstProject )
@Anonymous
Try these calculated columns
First Project = VAR mydate = CALCULATE ( MIN ( Table2[Project Start Date] ), FILTER ( CALCULATETABLE ( VALUES ( Table2 ) ), Table2[Project Start Date] >= Table1[Date in Service] ) ) RETURN CALCULATE ( MIN ( Table2[name Project] ), FILTER ( CALCULATETABLE ( VALUES ( Table2 ) ), Table2[Project Start Date] = mydate ) )
@Anonymous
Second Project = VAR FirstProject = Table1[First Project] VAR mydate = CALCULATE ( MIN ( Table2[Project Start Date] ), FILTER ( CALCULATETABLE ( VALUES ( Table2 ) ), Table2[Project Start Date] >= Table1[Date in Service] ), Table2[name Project] <> FirstProject ) RETURN CALCULATE ( MAX ( Table2[name Project] ), Table2[Project Start Date] = mydate )
Thank so much!
I also need the start date of the first and second project.
Can you help me with it?
@Anonymous
Try with these.
Date First Project = CALCULATE ( MIN ( Table2[Project Start Date] ), FILTER ( CALCULATETABLE ( VALUES ( Table2 ) ), Table2[Project Start Date] >= Table1[Date in Service] ) )
Date Second Project = CALCULATE ( MIN ( Table2[Project Start Date] ), FILTER ( CALCULATETABLE ( VALUES ( Table2 ) ), Table2[Project Start Date] >= Table1[Date in Service] ), Table2[name Project] <> FirstProject )
User | Count |
---|---|
96 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |