Forum Discussion
Compare Dates by Index
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!
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
2 Replies
- amitchandak
Super User
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")
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
