get the previous row value -- Need help

[Chandler_Bing]

Hello there,

i'm working with a dataset which involves choosing the values dynamically.

Example:

Fiscal year          Current date

2018-08-01           2018-10-01

2018-09-01           2018-10-01

2018-10-01           2018-10-01

2018-11-01           2018-10-01

2018-12-01           2018-10-01

Now when ever there is a match of the fiscal year with the current date I need to get the values from the previous row of the match till the end.

In the above example the result i'm trying to get is :

Fiscal year:

2018-09-01

2018-10-01

2018-11-01

2018-12-01.

Can anyone help me with this please. I think i need to use the multi row formula tool. But not sure about the formula.

Thanks

[danrh]

I'm not positive on the requirements, but is this more or less what you're looking for?

IF !IsNull([Row-1:Result]) THEN [Fiscal Year]
ELSEIF [Row+1:Fiscal Year]=[Row+1:Current date] THEN [Fiscal Year]
ELSE Null() ENDIF

[Chandler_Bing]

@danrh Thank you for the quick response.

That is exactly what i'm looking for however when I apply the formula in my workflow i'm not getting the output.

I'm attaching the workflow please tell me what do i need to change in it.

[danrh · Accepted solution]

I'm not able to run your workflow, but just looking at it the issue looks to be that the Fiscal Year periods are all on the first and the Current Date is the current date (go figure...) so they'll only be equal on the first of each month.

You can either change the Current Date to the first of the month, or you can alter the formula to something like:

IF !IsNull([Row-1:Result]) THEN [Fiscal Year]
ELSEIF DateTimeFormat([Row+1:Fiscal Year],'%b %Y')=DateTimeFormat([Row+1:Current date],'%b %Y') THEN [Fiscal Year]
ELSE Null() ENDIF

This will compare the month and year of each field, rather than the exact date.

[Chandler_Bing]

Thank you @danrh 

That works like a charm!

Cheers!