Find Last Day of Each Month and Apply Value

[anovohatski1]

Good afternoon,

Hoping that someone could help me here. I am trying to find the last available date within each month of data, then take value and apply it to all of the dates within each month. 

For instance, if I have January 31st value is 9 then i would apply it to all days within January (see Desired Value column). However, if in March the latest day that I have is March 10 - then I would use the value from that day and apply it all prior days within March. Hope it makes sense.

Is this even possible in Alteryx?

Dataset

Date	Value	Desired Value
1/1/20	2	9
1/2/20	3	9
1/3/20	4	9
1/31/20	9	9
2/1/20	5	7
2/2/20	6	7
2/27/20	8	7
2/28/20	7	7
3/1/20	8	3
3/2/20	4	3
3/9/20	6	3
3/10/20	3	3

[CharlieS · Accepted solution]

Hi @anovohatski1 

This is how I would go about doing this:

- Convert the "1/1/20" values into the standard date format of "2020-01-01" so DateTime functions in Alteryx can be used and sorting is ordered chronologically. 

- Use these date values to identify the month that each record falls within.

- Sort the records by date and use a Sample tool to find the last record available for each month.

- Join that value to the original records using the month field.

Check out the attached workflow to see this in action and let me know if you have any questions. 

[anovohatski1]

@CharlieS Thank you!