Find latest status of repetitive incident

[Abdel_Spateof]

Hello

This is my second participation in this forum; I am still learning how power BI works; 

Thanks for helping me with the below case

I have 2 Tables, 

1) TimeTracking Table containing Worklogs 

Key	Worklog ID	Worklog Creation Date	Time Entry Logged Time	STATUS_Value?
IMIS-205	12107	09/10/2018 10:17:30	0.5666666667
IMIS-205	12105	08/10/2018 18:38:51	0.9666666667
IMIS-206	12106	08/10/2018 18:38:54	0.2
IMIS-205	12104	08/10/2018 18:38:50	0.2833333333
IMIS-205	12103	08/10/2018 16:38:28	1.1
IMIS-205	12102	08/10/2018 16:38:27	0.15
IMIS-205	12100	08/10/2018 15:04:18	0.1333333333
IMIS-205	12101	08/10/2018 16:38:26	0.1333333333
IMIS-206	12099	08/10/2018 14:56:44	0.7666666667
IMIS-206	12098	08/10/2018 14:38:27	0.7
IMIS-206	12097	08/10/2018 12:03:41	0.9
IMIS-205	12096	08/10/2018 12:03:39	0.0666666667
IMIS-206	12095	08/10/2018 11:04:34	0.0333333333
IMIS-206	12094	08/10/2018 10:51:44	0.6166666667
IMIS-205	12093	08/10/2018 10:38:24	0.2666666667
IMIS-205	12092	08/10/2018 10:38:23	0.25
IMIS-196	12091	05/10/2018 18:07:46	0.0666666667
IMIS-196	12090	05/10/2018 18:07:46	1.033333333
IMIS-196	12089	05/10/2018 18:07:46	1.583333333
IMIS-196	12088	05/10/2018 13:39:04	0.45
IMIS-196	12087	05/10/2018 11:39:39	0.15
IMIS-196	12085	05/10/2018 11:14:48	0.0833333333
IMIS-196	12086	05/10/2018 11:19:20	0.0833333333
IMIS-196	12084	05/10/2018 10:38:41	1.066666667
IMIS-196	12083	04/10/2018 18:51:39	0.8

2) History Table containing the status Change

Key	CHANGE_on	Status_Value	Change_effective_from
IMIS-206	status	Elab-Review	08/10/2018 17:06:27
IMIS-161	status	Reviewed	08/10/2018 16:23:42
IMIS-197	status	review	08/10/2018 15:04:28
TA-3	status	To do	08/10/2018 14:39:02
TA-2	status	To do	08/10/2018 14:38:47
TA-1	status	To do	08/10/2018 14:38:12
IRC-20	status	Selected	08/10/2018 14:10:47
IMIS-206	status	Elaboration	08/10/2018 11:12:44
IMIS-206	status	Screen	08/10/2018 10:13:10
IMIS-206	status	Open	08/10/2018 10:12:17
IMIS-205	status	Screen	08/10/2018 9:33:38
IMIS-205	status	Open	08/10/2018 9:33:32

I need to know what was the status of a key During entry of a worklog , what should be the best function that could search in the status table and provide me with the status?

you can find the full sample data in the link below

https://docs.google.com/spreadsheets/d/1VoANXMB_gTpNWORqgW7_Jm_u2gHCCGqLoh98sUSQzjc/edit?usp=sharing

[v-piga-msft]

Hi @Abdel_Spateof,

I'm a little confused about your requirement, could you share your desired output so that I can understand your logic and get the solution?

Best  Regards,

Cherry

[Abdel_Spateof]

Hello @v-piga-msft

For the first Table; I need to fill the Column with a header in Red which represents the Status

in the first table, when a worklog ID Happens; it happens in during a time status ( Represented in Table 2)

for Every Worklog ID, I need to compare the worklog creation Date of that Key with the Change Effective From , 

e.g Worklog ID 12xx happened when Key XXXX was in "Screen"

[Zubair_Muhammad · Accepted solution]

@Abdel_Spateof

Try this column.

See attached file as well

Status =
MINX (
    TOPN (
        1,
        FILTER (
            History_Table,
            History_Table[Key] = Time_Tracking_Table[Key]
                && History_Table[Change_effective_from]
                < Time_Tracking_Table[Worklog Creation Date]
        ),
        [Change_effective_from], DESC
    ),
    [Status_Value]
)