How to show look up a value of the previous month

[windylee]

Here is my data table:

Buyer ID	Buyer Name	Credit Limit ($)	File Date
001	ABC	2000	2024-01
001	ABC	1000	2023-12
001	ABC	1000	2023-11
002	DEF	5000	2024-01
003	GHI	4000	2023-12
003	GHI	3500	2023-11

I want to add a column to get the credit limit of last month of File Date, so that the new table will be:

Buyer ID	Buyer Name	Credit Limit ($)	File Date	Last Month Credit Limit($)
001	ABC	2000	2024-01	1000
001	ABC	1000	2023-12	9000
001	ABC	900	2023-11
002	DEF	5000	2024-01
003	GHI	4000	2023-12	3500
003	GHI	3500	2023-11

Could you please teach me what DAX I can use in order to get the new column?

I tried to create a new column to show Last File Date using PREVIOUSMONTH, then add another column to get the limit by using LOOKUPVALUE, but there are circular dependencies detected.

Please help! Thank you very much!

[ryan_mayu · Accepted solution]

@windylee 

make sure the file date is in date type and create a column

Column = maxx(FILTER('Table','Table'[Buyer ID]=EARLIER('Table'[Buyer ID])&&'Table'[File Date]=EDATE(EARLIER('Table'[File Date]),-1)),'Table'[Credit Limit ($)])

[windylee]

Thank you so much!! I successfully added the desired column!

[ryan_mayu]

you are welcome