Lookup for the value of previous month in the same table

[Anonymous]

HI,

I have been struggling now for a while with creating a new column. I have the following columns in my table:

Company |  Sales  |  Date

I created a new column to the query that gives also the date exactly one month before, named PreviousMonthDate. All the sales of the month are dated on the first day of the month. I would like to create a new column, that would show the Sales from the previous month. I tried the following DAX formula:

PreviousMonthSales = LOOKUPVALUE(Table[Sales]; Table[Company]; Table[Company]; Table[Date]; Table[PreviousMonthDate])

I get an error: "A table of multiple values was supplied where a single value was expected." 

How should do this? The table is structured so that there is one row for each company each month,

[anandav · Accepted solution]

Try the below DAX for the new column: