How to get a Date Value from unrelated tables

[Anonymous]

I have two unrelated tables named TableA and TableB.  I am trying to get the earliest date from TableB into a calculated column in TableA and I keep getting the incorrect date.   I value should be 3/15/2020 but using the formula below I receive 01/15/2020.

GetDate = CALCULATE(MIN(TableB[CvPremExpDate]), TREATAS(
SUMMARIZE(
TableA,TableA[PL_POL_NBR],
TableA[PL_LOCATION_NBR],
TableA[PL_RISK_NBR],
TableA[GetMaxEndr]
),
TableB[PL_POL_NBR],
TableB[PL_LOCATION_NBR],
TableB[PL_RISK_NBR],
TableB[CV_ENDR_NBR]))

TableA

PL_POL_NBR	PL_LOCATION_NBR	PL_RISK_NBR	RK_VEHICLE_NBR	GetMaxENDR
00000151854	9999	9665	33	10
00000151854	9999	9666	33	10
00000151854	9999	9667	33	10
00000151854	9999	9668	33	10
00000151854	9999	9669	33	10
00000151854	9999	9670	33	10
00000151854	9999	9671	33	10
00000151854	9999	9672	33	10
00000151854	9999	9673	33	10
00000151854	9999	9674	33	10

TableB

PL_POL_NBR	PL_LOCATION_NBR	PL_RISK_NBR	CV_ENDR_NBR	CvPremExpDate
151854	9999	9665	9	10/8/2020
151854	9999	9665	10	3/15/2020
151854	9999	9666	9	10/8/2020
151854	9999	9666	10	3/15/2020
151854	9999	9667	9	10/8/2020
151854	9999	9667	10	3/15/2020
151854	9999	9668	9	10/8/2020
151854	9999	9668	10	3/15/2020
151854	9999	9669	9	10/8/2020
151854	9999	9669	10	3/15/2020
151854	9999	9670	9	10/8/2020
151854	9999	9670	10	3/15/2020
151854	9999	9671	9	10/8/2020
151854	9999	9671	10	3/15/2020
151854	9999	9672	9	10/8/2020
151854	9999	9672	10	3/15/2020
151854	9999	9673	9	10/8/2020
151854	9999	9673	10	3/15/2020
151854	9999	9674	9	10/8/2020
151854	9999	9674	10	3/15/2020

[amitchandak · Accepted solution]

@Anonymous , Create a new column in tableA

minx(filter(Tableb, Tableb[PL_POL_NBR]=Tablea[PL_POL_NBR] && Tableb[PL_LOCATION_NBR]=Tablea[PL_LOCATION_NBR] && Tableb[PL_RISK_NBR] =Tablea[PL_RISK_NBR]), Tableb[CvPremExpDate])

[ryan_mayu]

@Anonymous 

please try this

Column = MINX(FILTER(TableB,TableA[PL_POL_NBR]=TableB[PL_POL_NBR]),'TableB'[CvPremExpDate])

[Anonymous]

Tried your solution and I get the same incorrect date as my formula.  I get 01/15/2020 instead of 03/15/2020

[lbendlin]

This should work

GetDate = CALCULATE(MIN(TableB[CvPremExpDate]), TREATAS(
SELECTCOLUMNS(TableA,
"PL_POL_NBR",TableA[PL_POL_NBR],
"PL_LOCATION_NBR",TableA[PL_LOCATION_NBR]
,"PL_RISK_NBR",TableA[PL_RISK_NBR]
,"CV_ENDR_NBR",TableA[GetMaxENDR]),
TableB[PL_POL_NBR],
TableB[PL_LOCATION_NBR],
TableB[PL_RISK_NBR],
TableB[CV_ENDR_NBR]))

[Anonymous]

Tried your suggestion and I get the same incorrect date as my original formula.  The date returned is 01/15/2020 when it should be 03/15/2020.