Find or refer to previous value using the date column

Sorry, but I don't get it:

You want to get a value from a previous row. But only within a subset of your data. According to your image where your desired result is in red colours, these subsets are defined by 2 columns: Reporting Frequency and Index (including: "DAX", "UKX" and "NKY") (although they don't make a difference in this example: It could be either Frequency or Index).

This unfortunate naming requires the real Index that is required to define and keep the sorting-order to be named "Indexed".

So you either merge your 2 subset/groups-defining columns into one in order to deal with one key-column (that would also make your DAX-life easier: "DAX-Monthly", "UKX-Quarterly" aso) or you adjust the DAX-formula like this:

DAX:

Column = CALCULATE(SUM('YourTable'[Value]),

FILTER('YourTable', YourTable[Indexed]=EARLIER(YourTable[Indexed])+1)

&& YourTable[Index]=EARLIER(YourTable[Index])

&& YourTable[Reporting Frequency] = EARLIER(YourTable[Reporting Frequency])

)

EARLIER reading here as: The same value like in the current row.

In any case: You need to use M (the query-editor) to add an Index-column that reflects the sort-order of your input data (here called "Indexed"). That piece seems to be missing in the table from your latest post.

Hi Imke,

Thanks for the explanation.  I was using the Microsoft Edge Browser and that issue was the same.  When i copyied the code from firefox I didn't get the errors but i now have a number of fields which need completing.

Can i ask you what i should do at this stage?

Thanks,

Seb

Just leave it standing as it is. It will just be used for reference by formula I've provided in my previous post:

" ...

This will create a query in form of a function. Name it "fnPreviousRowValue".

In order to call this function, you add a column to your table in the query editor and pass the parameters to it like this in the customs column formula editor:

=fnPreviousRowValue(NameOfPreviousStep, "Value", "Date", "Index")

..."

ok i have done that but now get the following error. 

Sorry to do this to you but do you know i should do next.  Feels like we are gettting closer.

I forgot to mention that I voted up your bug issue that you mentioned before.  Hopefully it will get pushed forward.

Sorry - just recognized that my previous instructions were wrong. You don't call that function in a custom column, but as a separate step. So just replace the code in the formula-editor from your screenshot with this:

=fnPreviousRowValue(#"Changed Type5", "Value", "Date", "Index")

So the content of your previous step (which is a table) will be passed as the first argument to this function.

Having problems to read the picture as the focus/enlargement doesn't work. Does it work with you?

Your instruction worked but then i selected previous row and this error popped up

There shouldn't be an Expand-operation. The results should directly be returned in the table-format.

Please share code (of previous steps) if it doesn't work.

I think the fastest approach is a mix between M and DAX: Add the index in M and then a calculated column using DAX. But that would mean that you cannot go back to the query-editor (M) in order to perform further transformations.

DAX:

Column = CALCULATE(SUM('YourTable'[Value]), FILTER('YourTable', YourTable[Indexed]=EARLIER(YourTable[Indexed])+1) && YourTable[Index]=EARLIER(YourTable[Index]))

Where EARLIER is returning the value the current row of the respective columns. You add 1 on your Indexed, because it seems that you've applied that order in your [Indexed]-column. Normally you would subtract 1 in order to get the previous row.

This isn't working becuase the filter is applied to the column which is indexing subcategories within the dataset.  This is the solution you provided during the week.  The calculation therefore needs to filter 2 columns, first the category which is being indexed and then the indexed column.  Below is an example of the dataset

Date              Name                     Index                   Reporting Frequency

1/1/16           A                            1                          Monthly

1/2/16           A                            2                          Monthly

1/1/16           B                            1                          Quarterly

1/4/16           B                            2                          Quarterly

1/1/16           C                            1                          6 Monthly

1/7/16          C                             2                          6 Monthly