Compare Dates by Index

[jaybertx]

Hi everyone!

I have been hammering on this for a few weeks and every time I think I have the answer I end up going in circles. Hopefully someone can help point me in the right direction. I have data that looks like this: 

Payroll ID	Orientation Date	Orientation Index	Count?
1231	1/1/2023	1	Y
1232	1/14/2023	1	Y
1232	8/14/2023	2	Y
1232	10/14/2023	3	N
1233	3/1/2023	1	Y
1233	8/1/2023	2	N

The "Count" column does not currently exist but I would like it to be generated automatically. Rules for Y/N: 

1. I would always like to count the index of 1

2. For a specific payroll ID I would like to count an orientation if there is 6 months or more between them. I would only compare 1 to 2, 2 to 3, 3 to 4, etc. 

Any help would be appreciated! Thanks!

[Anonymous · Accepted solution]

Hi  @jaybertx ,

Here are the steps you can follow：

1. Create calculated column.

Count =
var _next=
MAXX(
    FILTER(ALL('Table'),'Table'[Payroll ID]=EARLIER('Table'[Payroll ID])&&'Table'[Orientation Index]=EARLIER('Table'[Orientation Index])+1),[Orientation Index])
var _nextdate=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Payroll ID]=EARLIER('Table'[Payroll ID])&&'Table'[Orientation Index]=_next),[Orientation Date])
var _datediff=
DATEDIFF(
    'Table'[Orientation Date],_nextdate,MONTH)
return
IF(
    'Table'[Orientation Index]=1,"Y",
    IF(
        _datediff>=6,"Y","N"))

2. Result:

When [Payroll ID]=1233 and [Orientation Index]=2, the date difference from [Orientation Index]=3 is only 2 months, which is less than 6 months, so it is N

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@jaybertx , Based on what I got. A new column

New column =

Var _max  = maxx(filter(Table, [Payroll ID] = earlier([Payroll ID]) && [Orientation Date] < earlier([Orientation Date])  )[Orientation Date] )

return

if(datediff(_max, [Orientation Date], month) >= 6, "Y", "N")