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

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-printable-charact 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.

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

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.

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.

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

Find or refer to previous value using the date column

22 Replies

Habib
Continued Contributor
10 years ago
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
  Helper III
  10 years ago
  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
    Continued Contributor
    10 years ago
    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

Forum Discussion

Find or refer to previous value using the date column

22 Replies

Related content

Referring Same Column - Previous Value

UDF with variable table reference and fixed column reference

DAXUDF Reference a column

Reference Previous Value in adjacent column

Using column value as column reference