Recover info from previous date event

[babajat]

Hi!

So, i've got this data:

Company 1	Company 2	Date	InfoCompany 1	InfoCompany 2
My company	xx	31/12/2022	6	11
My company	gg	30/12/2022	4	22
ee	aa	30/12/2022	4	7
My company	vv	28/12/2022	88	4
mn	My company	29/12/2022	2	33

I'm trying to add some column that search for the lates row in wich Company1 appears and recover the corresponding info.

Here's and example:

Company 1	Company 2	Date	InfoCompany 1	InfoCompany 2	Previousdate	PreviousInfo	2ndPreviousdate	2ndPreviousInfo	3rdPreviousdate	3dPreviousInfo
My company	xx	31/12/2022	6	11	30/12/2022	4	29/12/2022	33	28/12/2022	88
My company	gg	30/12/2022	4	22	29/12/2022	33	28/12/2022	88
ee	aa	30/12/2022	4	7
My company	vv	28/12/2022	88	4
mn	My company	29/12/2022	2	33

These are the dax formula wich i'm trying to use:

PreviousDate (Everything seems ok)

PreviousDate = 
VAR CompanyOI = 'Table'[Company 1]
VAR DateOI = 'Table'[Date]
VAR FilterOI = FILTER('Table', ('Table'[Company 1] = CompanyOI || 'Table'[Company 2] = CompanyOI) && 'Table'[Date] < DateOI)
VAR _prev_date = MAXX(FilterOI, [Date])
return
MINX(FILTER(FilterOI, 'Table'[Date] = _prev_date), 'Table'[Date])

PreviousInfo (Everything seems ok)

PreviousInfo = 
VAR CompanyOI = 'Table'[Company 1]
VAR DateOI = 'Table'[PreviousDate]
VAR FilterOI = FILTER('Table', ('Table'[Company 1] = CompanyOI || 'Table'[Company 2] = CompanyOI) && 'Table'[Date] = DateOI)
VAR _prev_date = MAXX(FilterOI, [Date])
return
MINX(FILTER(FilterOI, 'Table'[Date] = _prev_date), IF('Table'[Company1] = CompanyOI, 'Table'[InfoCompany 1], 'Table'[InfoCompany 2])

2ndPreviousDate (Everything seems ok)

2ndPreviousDate = 
VAR CompanyOI = 'Table'[Company 1]
VAR DateOI = 'Table'[PreviousDate]
VAR FilterOI = FILTER('Table', ('Table'[Company 1] = CompanyOI || 'Table'[Company 2] = CompanyOI) && 'Table'[Date] < DateOI)
VAR _prev_date = MAXX(FilterOI, [Date])
return
MINX(FILTER(FilterOI, 'Table'[Date] = _prev_date), 'Table'[Date])

2ndPreviousInfo (Giving me wrong results)

2ndPreviousInfo = 
VAR CompanyOI = 'Table'[Company 1]
VAR DateOI = 'Table'[2ndPreviousDate]
VAR FilterOI = FILTER('Table', ('Table'[Company 1] = CompanyOI || 'Table'[Company 2] = CompanyOI) && 'Table'[Date] = DateOI)
VAR _prev_date = MAXX(FilterOI, [Date])
return
MINX(FILTER(FilterOI, 'Table'[Date] = _prev_date), IF('Table'[Company1] = CompanyOI, 'Table'[InfoCompany 1], 'Table'[InfoCompany 2])

I can't wrap my head around what's wrong and i hope you can help me!

Thanks in advance

[Sahir_Maharaj]

Hello @babajat,

Can you please try this updated formula for 2ndPreviousInfo:

2ndPreviousInfo = 
VAR CompanyOI = 'Table'[Company 1]
VAR DateOI = EARLIER('Table'[2ndPreviousDate])
VAR FilterOI = FILTER('Table', ('Table'[Company 1] = CompanyOI || 'Table'[Company 2] = CompanyOI) && 'Table'[Date] = DateOI)
VAR _prev_date = MAXX(FilterOI, [Date])
RETURN MINX(FILTER(FilterOI, 'Table'[Date] = _prev_date), IF('Table'[Company 1] = CompanyOI, 'Table'[InfoCompany 1], 'Table'[InfoCompany 2]))