Forum Discussion

Tinus1905's avatar
Tinus1905
Resolver I
2 years ago

First value to find after column value

Hi,

 

In PowerBi query editor I want to find the first value based on date and name. 

This is what I have:

 

DateEmployeeHoursSick
1-1-2024Dave81
2-1-2024Dave61
3-1-2024Dave81
4-1-2024Dave41
5-1-2024Dave30
6-1-2024Dave80
7-1-2024Dave80
8-1-2024Dave80
9-1-2024Dave80
10-1-2024Dave80
11-1-2024Dave80
12-1-2024Caroline80
13-1-2024Caroline80
14-1-2024Caroline80
15-1-2024Caroline80
16-1-2024Caroline80
17-1-2024Caroline80
18-1-2024Caroline80
19-1-2024Caroline80
20-1-2024Caroline80
21-1-2024Caroline81
22-1-2024Caroline81
23-1-2024Caroline81
24-1-2024Caroline81
25-1-2024Caroline80
26-1-2024Dave80
27-1-2024Dave81
28-1-2024Dave81
29-1-2024Dave81
30-1-2024Dave80
31-1-2024Dave80
1-2-2024Dave80
2-2-2024Dave80
3-2-2024Dave80
4-2-2024Dave80
5-2-2024Dave80
6-2-2024Dave80
7-2-2024Dave80
8-2-2024Dave80
9-2-2024Dave80
10-2-2024Dave81
11-2-2024Dave81
12-2-2024Dave81
13-2-2024Dave80

 

And this is what I want:

 

DateEmployeeHoursSickFirst_sickSick_hoursWorking_hours
1-1-2024Dave818  
2-1-2024Dave61 6 
3-1-2024Dave81 8 
4-1-2024Dave41 4 
5-1-2024Dave30  3
6-1-2024Dave80  8
7-1-2024Dave80  8
8-1-2024Dave80  8
9-1-2024Dave80  8
10-1-2024Dave80  8
11-1-2024Dave80  8
12-1-2024Caroline80  8
13-1-2024Caroline80  8
14-1-2024Caroline80  8
15-1-2024Caroline80  8
16-1-2024Caroline80  8
17-1-2024Caroline80  8
18-1-2024Caroline80  8
19-1-2024Caroline80  8
20-1-2024Caroline80  8
21-1-2024Caroline818  
22-1-2024Caroline81 8 
23-1-2024Caroline81 8 
24-1-2024Caroline81 8 
25-1-2024Caroline80  8
26-1-2024Dave80  8
27-1-2024Dave818  
28-1-2024Dave81 8 
29-1-2024Dave81 8 
30-1-2024Dave80  8
31-1-2024Dave80  8
1-2-2024Dave80  8
2-2-2024Dave80  8
3-2-2024Dave80  8
4-2-2024Dave80  8
5-2-2024Dave80  8
6-2-2024Dave80  8
7-2-2024Dave80  8
8-2-2024Dave80  8
9-2-2024Dave80  8
10-2-2024Dave818  
11-2-2024Dave81 8 
12-2-2024Dave81 8 
13-2-2024Dave80  8

 

So the first time an employee is sick after a working day, then a column with 'First_sick'. 

Need Help

3 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    2 years ago

    Hi Tinus1905 , 
    You can try using:- =
    if [Sick] = 1 and ([Employee] <> #"Shifted Row"[Employee] or #"Shifted Row"[Sick] = 0) then [Hours] else null

    and for Sick hours:-

    = if [First_sick] <> null then [First_sick] else null
    and for working hours.
    = if [First_sick] = null then [Hours] else null
    This should give you the appropriate columns.
    Thank you

    • Tinus1905's avatar
      Tinus1905
      Resolver I
      2 years ago

      grazitti_sapna when I use if [Sick] = 1 and ([Employee] <> #"Shifted Row"[Employee] or #"Shifted Row"[Sick] = 0) then [Hours] else null in a custom column, I get the same results as column "Hours". 

      The result must be: