This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email firstname.lastname@example.org for assistance.
"Closest to the first of the month". Which "first of the month"? Meaning, relative to what month? First that question has to be answered. Once you have that answer, you can use the DateTimeTrim([month],'firstofmonth') function, and then use a DateTimeDiff function to calc the number of days between that first of the month and your value, and then take the minimum but using a summarize tool and join.
But first we'd need to know which "month" you want to get to the first of.
If you want to calculate this, you can first use a DateTimeFormat([DATE],'%Y%m') which will give you a format like 202101 for this month, then sort yoiur data by this new field first and then by the dates itself.
And finally use a Sample tool to get the first record for each "period"
I am a bit confused by your original example, but I believe I understand what you're looking for.
I started off by converting your values to actual date formats. This automatically made the dates in the year 1400, but the year doesn't matter that much in your case. I then derived the month from the date using a formula. I used a sort to sort by the month and date, so each group of months was together, and the dates were going in ascending order. This allowed me to use a sample tool to grab the first value per month, effectively being the date closest to the beginning of the month.