Forum Discussion

sebbyp's avatar
sebbyp
Icon for Helper III rankHelper III
10 years ago

Find or refer to previous value using the date column

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,...
sebbyp's avatar
sebbyp
Icon for Helper III rankHelper III
10 years ago

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's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
10 years ago

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.