How to Select or Filter dates that are closest to end of current month

[jaypee1217]

I've created a workflow that calculates a payroll accrual file for different Batch IDs. I'm trying to take it to the next level where if there are multiple dates the workflow selects/filters the closest date to the end of the current month. This would represent the population that the remainder of the workflow would then calculate an accrual. 

I'm stuck at this point. I don't know how to filter/select the smallest value in [Diff to End of Current Month] for each Batch ID row. 

Batch ID	Pay Period End Date	Current Month	Diff to End of Current Month
A	4/14/2023	4/30/2023	16
A	4/28/2023	4/30/2023	2
B	4/5/2023	4/30/2023	25
B	4/15/2023	4/30/2023	15
C	4/20/2023	4/30/2023	10
C	4/12/2023	4/30/2023	18

Desired Output

Batch ID	Pay Period End Date	Current Month
A	4/28/2023	4/30/2023
B	4/15/2023	4/30/2023
C	4/20/2023	4/30/2023

[ScottLewis · Accepted solution]

There may be a more elegant solution but this works. Summarize on min of difference, join on min difference and batch ID. 

[EdwardCochrane]

Can you use a sort tool and then a sample tool?

[jaypee1217]

i went down the sort and sample path, but i couldnt get around the variable of different batch id's and variable smallest value. 

[jaypee1217]

Thanks Scott. Let me give this a whirl. 

[binuacs]

@jaypee1217 Another option convert your date fields into Alteryx Date type then find the maximum date using the summarize tool and join the original input file

[prakashkaur189]

I have tried a different approach. Download the attached flow and see if this helps.

[jaypee1217]

Thank you everyone for the prompt responses. Scott's worked in my data set and is the simplest to insert into my overall model. 

[jaypee1217]

This is awesome. Just ran the updated workflow and it matched my original output. Now the user doesn't have to visually choose (around 30 out of 70-80 files) input files that are the last pay date in a month. Select ALL and now the output will be just the last pay period data. woot woot.