I cannot retrieve Prior Month Data

[LornaB74]

Hello

This is my first time posting a question and I'm quite new to the Alteryx world, so forgive me if I'm being very thick! Please see table of data attached. I am having an absolute brain melt today. I need to retrieve the prior month data from this list - based upon the latest record - 1. So it can be on the RM Version column or the Cut Off Date.

I need the Run_Total, Forecast_Margin and Forecast_Margin% Columns.

I have managed to bring out the 1st and the latest by using min and max in Summary, but how do I find the max - 1? 

Any help would be much appreciated

Thanks

LornaB

[Emil_Kos]

Hi @LornaB74,

You need to use this formula in order to achieve your goal:

DateTimeAdd([Cut Off Date],-1,'month')

Please mark as a solution if this is helpful.

Good luck!

[LornaB74]

Hiya, thank you for this.

This returns the previous month date, but I need to return the values of the other columns as a value based upon the prior month date, does that make sense?

[Qiu]

@LornaB74 
If I understand your intention correctly.

[Emil_Kos · Accepted solution]

Hi @LornaB74,

Please see my workflow attachted:

Please mark as a solution if this is helpful!

[LornaB74]

Thank you, that is brilliant!

Cheers

[LornaB74]

Thank you Qiu

This would work equally as well as the other solution! That's fantastic, thank you

[Emil_Kos]

Hi @LornaB74,

thank you for marking my post as an solution.

Good luck!

[Qiu]

@LornaB74 
Thank you for the comment.

Warm note, multiple accept is allowed here. 😁