How to find 2 successive dates and calculate no of hours on those?

[Anmolgan]

I need to write the DAX function using which I can find number of dates that are coming first 2 times for each of the employee and what are the hours worked, below is the data like:

EmpName   Date              hours

A                 23/10/2021    4

A                 30/10/2021    6

B                  01/11/2021    3

C                  05/11/2021     10

C                  14/11/2021     11

C                  20/11/2021     20

DAX should give me number of hours as A=10 and C= 22 (since we are talking about first 2 dates per employee), and B will not be counted in the above since there are no 2 consecutive dates.

Any ideas how to calculate the above?

[amitchandak]

@Anmolgan , Create these two columns

Cnt = countx(filter(Table, [EmpName] = earlier([EmpName]) && [Date] <= earlier([Date]) ), [EmpName])

total = countx(filter(Table, [EmpName] = earlier([EmpName]) && [Date] <= earlier([Date]) ), [hours])

create meausre

calculate(sum(Table[Total]), filter(Table, Table[cnt] =2))

[Anmolgan]

@amitchandak  thanks for the response, can we tweek this as I have payperiods defined by the below DAX, so basically am looking to find number of employees worked less then 20 hours in there first two payperiods, now payperiods can be 3,5,10 etc. Below are the DAX have written to determine payperiod:

pay period number = FLOOR(DATEDIFF(DATE(2021,8,15),'Dates'[Date],DAY)/14,1)+1

pay period end date = CALCULATE(MAX('Dates'[Date]),ALLEXCEPT(Dates,Dates[pay period number]))

[amitchandak]

@Anmolgan , do you need based on selected range or only the first two periods?

You can first 2 period without slicer filter, columns will do

additional filter will do

calculate(sum(Table[Total]), filter(Table, Table[cnt] =2 && [Total] >20 ))

[Anmolgan]

@amitchandak  I Require only the first two periods to check what were the hours worked does the DAX you posted previously will do this?

[amitchandak · Accepted solution]

@Anmolgan , you can try a flag like

Column = Var _count = countx(filter(Period, [EmpNam] =EARLIER([EmpNam]) && [Date] <= EARLIER([Date])), [EmpNam]) 
var _hour = sumx(filter(Period, [EmpNam] =EARLIER([EmpNam]) && [Date] <= EARLIER([Date])), [hours]) 
return if(_count =2 && _hour >20, 1,0)

See if attached file after signature can help