calculate value (through measure and calculated column) of one period earlier without a date table

[Anonymous]

Hi,

I would like to calculate the value of a period earlier but without using a seperate date table.

Instead of a date column, I created a seperate column with a lists quarters (called 'datesortnumber') but they are mentioned as 1, 2, 3, 4, 5, 6 etc.

I would like to display the value whereby the datesort period is one earlier.

This I would like to achieve with a measures as well as through a calculated column.

A very simplified version of my input table:

VALUE, DATESORTNUMBER
111, 1
112, 2
113, 3
114, 4
115, 5

The result should look like:
DATESORTNUMBER, RESULTVALUE
1, no result (because no value at datesortnumber 0)
2, 111
3, 112
4, 113
5, 114

How can I achieve to create this result value column whereby the value is one datesort earlier (through a measure and a calculated column?  Thank you for the help!

[Greg_Deckler · Accepted solution]

@Anonymous Try:

Measure =
  VAR __DateSortNum = MAX('Table'[DATESORTNUMBER])
RETURN
  MAXX(FILTER(ALL('Table'),[DATESORTNUMBER] = __DateSortNum - 1),[VALUE])


Column = 
  VAR __DateSortNum = 'Table'[DATESORTNUMBER]
RETURN
  MAXX(FILTER(ALL('Table'),[DATESORTNUMBER] = __DateSortNum - 1),[VALUE])

[Anonymous]

Works perfect, thank you!