Find or refer to previous value using the date column

[sebbyp]

I would like to find or refer to the previous value in a time series so I can do a simple difference calculation between the current and the previous value.

The database i have has a date column, frequency, value.  Each time series has different reporting frequency such as monthly, quarterly, yearly.  I would like to be able to add another column in the database to show the prior value for the specific time series.  Is this possible when the database has varying reporting frequencies?

Any help would be much appreciated

[Habib]

If you want to refer to previous values in date hierarchy you can use DAX functions like PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR.

If you want to refer to value available in previous row then you can use EARLIER function.

[sebbyp]

Thanks for your response.  I cannot use previous because the collection of time series in the single database has different reporting frequencies.  So the collection of time series in the data set can be reported on a daily, monthly, quarterly annually.  This doesn't help with previous functions. 

I am not sure if the earlier function can work with this too.   Maybe i don't quite understand how this function works.  Please elaborate if you can.

Thanks

[Habib]

I am unable to understand you probelm. If you provide some sample data that would be great and will be easy to answer your question.

EARLIER help can be found here.

https://msdn.microsoft.com/en-us/library/ee634551.aspx

[sebbyp]

I have columns A,B & C.  I would like to create a formula to give the result in Column E

Hope you can help

[Habib]

Do you have any reference key to identify if reporting period is day, month, quarter or year? If yes, you can use SWITCH statement to identify what function you need to apply for PREVIOUS.

[sebbyp]

yes i do have the index reporting frequency registered.  How would you suggest using the PREVIOUS value as i couldn't get it working. 

[ImkeF]

Hi Sebastien,

this is a link to a very good description of the "previous-row-concept" in M (the query editor): http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/

If your table is not too large, you could add a clause where you check whether the "Index" (better give that column a new name...) of the current row equals the "Index" of the previous row and then do the subtraction. But if you table gets larger, you might run into performance-problems with this approach and then you would use the partitioning from the nested-index-method from the other post this morning and add a function to it like you can see in this video:

[sebbyp]

Hi Imke,

Thanks for your help on this one too.  I think the level of expertise is going beyond my comprehension.  I am really confused on this one and can't solve it which is really annoying because I thought this would be a relatively straight forward task. 

My data is now organised as in the image below.  I've tried to take everyones comments on board like including frequency and indexing to help do the calculation but i am stuck 😞

Do you have any other suggestions, videos or instructions which can help me.

Thanks,

Sebastien

[ImkeF]

Hi Sebastien,

I've created a function that does the trick. Just create a new query and copy this code into the advanced editor:

(Sourcetable, ValueColumn as text, SortColumn as text, optional Group as text) =>

let

// Internal function retrieving previous row
fnPreviousRow = 
(Source, Value_Column as text, Sort_Column as text) =>
let
    Source0 = Source,
    #"Sorted Rows" = Table.Buffer(Table.Sort(Source0,{{Sort_Column, Order.Ascending}})),
    AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "fnIndex", 0, 1),
    #"Added Index" = Table.AddIndexColumn(AddedIndex, "fnIndex2", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"fnIndex"},#"Added Index",{"fnIndex2"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {Value_Column}, {"PreviousRow"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"fnIndex2"})
in
    #"Removed Columns",

// Tow different code-versions depending on group yes/no

// 1 Code for group 
    #"Grouped Rows" = Table.Group(Sourcetable, {Group}, {{"Content", each Table.Buffer(_), type table}}),
    ExecuteFunction = Table.AddColumn(#"Grouped Rows", "Custom", each fnPreviousRow([Content], ValueColumn, SortColumn)),
    #"Removed Columns" = Table.RemoveColumns(ExecuteFunction,{"Content"}),
    ResultGrouped = Table.ExpandTableColumn(#"Removed Columns", "Custom", List.Difference(List.Union({Table.ColumnNames(Sourcetable), {"fnIndex", "PreviousRow"}}), {Group}), List.Difference(List.Union({Table.ColumnNames(Sourcetable), {"fnIndex", "PreviousRow"}}), {Group})),

// 2 Code without group
    ResultUngrouped = fnPreviousRow(Sourcetable, ValueColumn, SortColumn),

// Select Result
    FinalResult = if Group <> null then ResultGrouped else ResultUngrouped
in
    FinalResult

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")

[sebbyp]

HI Imke,

I have tried your suggestion but i get the following error.  I amo not sure if i am doing something wrong or there is something in the code you provided.

Why is it so complicated to calculate the difference from the previous value. Is there not a Dax formula i can do in a calculated column?

[ImkeF]

Hi Seb,

the problem with this totally un-understandable error-message lies in a bug in this forum-software if you use it with Internet Explorer: It inserts sth like non-printable characters into the code that destroys the commands. This is pure pain.

You have to check the strings that the error-message pops up, and double-click them. If only a part of the word will be highlighted you know you have found the delinquent and delete the next (invisible) character...

Have a look at the video:

Problem with JoinKind.LeftOuter was actually that the unprintable character was at the end of the string and that hasn't been removed when I retyped the string (only later when I replaced with 1 and re-replaced).

I've reported this error here: https://ideas.powerbi.com/forums/360879-issues/suggestions/15011475-code-editor-in-forum-injects-non... but the issue hasn't been solved yet. So please give that a vote.

So if you use a different browser to copy the code it should work fine.

This is pure waste of time, so sorry to say, but I will not be back for any help in the community until this is solved.

[sebbyp]

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

[ImkeF]

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")

..."

[sebbyp]

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.

[ImkeF]

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?

[sebbyp]

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

[ImkeF]

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.

[ImkeF]

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.

[sebbyp]

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

[ImkeF]

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.