Forum Discussion

babajat's avatar
babajat
Frequent Visitor
3 years ago

Recover info from previous date event

Hi!

So, i've got this data:

Company 1Company 2DateInfoCompany 1InfoCompany 2
My companyxx31/12/2022611
My companygg30/12/2022422
eeaa30/12/202247
My companyvv28/12/2022884
mnMy company29/12/2022233

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 1Company 2DateInfoCompany 1InfoCompany 2PreviousdatePreviousInfo2ndPreviousdate2ndPreviousInfo3rdPreviousdate3dPreviousInfo
My companyxx31/12/202261130/12/2022429/12/20223328/12/202288
My companygg30/12/202242229/12/20223328/12/202288  
eeaa30/12/202247      
My companyvv28/12/2022884      
mnMy company29/12/2022233      

 

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

Need Help

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    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]))