Duplicate value vlookup with date comparison in specified period with power query

[Neha94]

Hello,

I have 2 datasets - Set A and Set B.

Set A has Issue description and issue date along with product code. Set B has product code along with product validity start date and product validity end date. Since validation process requires revalidation of product after validity has ended, there are multiple validity start date and end dates in Set B. The final outcome which I want is for every issue in Set A, does the Set A issue date fall between any of the validity periods?

Please find example below-

Set A	Set A	Set A
Issue Date C1	Issue C2	Product code C3	Is issue date between validity period?(Outcome)	explanation
1-May-21	leakage	A	Yes	As it falls in second validity period of A (Mar'21-Mar'22)
6-Jul-21	rattle sound	B	No	Issue after both validity for B
21-Sep-21	electrical failure	C	Yes	Issue within second validty of C (Aug'21-Aug'22)
15-Oct-21	broken	D	No	Issue before validity of D
Set B C1	Set B C2	Set B C3
Product code	Validity Start	Validity end
A	1-Dec-19	1-Dec-20
A	3-Mar-21	3-Mar-22
A	6-Jun-14	6-Jun-15
B	9-Sep-14	9-Sep-15
B	10-Oct-16	10-Oct-17
C	4-Apr-19	4-Apr-20
C	8-Aug-21	8-Aug-22
D	29-Oct-21	29-Oct-22

[AlexisOlson · Accepted solution]

How about this as a calculated column?

VAR CurrProd = SetA[Product code]
VAR CurrDate = SetA[Issue Date]
VAR SetBFiltered =
    FILTER (
        SetB,
        SetB[Prodcut code] = CurrProd
            && SetB[Validity Start] <= CurrDate
            && SetB[Validity End] >= CurrDate
    )
RETURN
    IF ( ISEMPTY ( SetBFiltered ), "No", "Yes" )

[smpa01 · Accepted solution]

@Neha94 use the following measure, works beyond any relationship

Measure =
VAR _issue =
    MAX ( SetA[Issue Date] )
VAR _filt =
    CALCULATE (
        MAX ( SetB[Product code] ),
        FILTER ( SetB, _issue >= SetB[Validity Start] && _issue <= SetB[Validity end] ),
        TREATAS ( { MAX ( SetA[Product code] ) }, SetB[Product code] )
    )
RETURN
    IF ( ISBLANK ( _filt ) = TRUE (), FALSE (), TRUE () )

[Neha94]

Yes. This worked. Thanks a lot. Can you help me understand how does it check all the different validity periods for a single product? Thanks again!

[smpa01]

Hi @Neha94 

Measure =
VAR _issue =
    MAX ( SetA[Issue Date] ) --- what is the currently visible SetA[Issue Date] from the filter context
VAR _filt =
    CALCULATE (
        MAX ( SetB[Product code] ),
        --- after all the necessary filtering give the max date ---step3
        FILTER (
            SetB,
            _issue >= SetB[Validity Start]
                && _issue <= SetB[Validity end]
        ),
        -- filter SetB so that SetB[Validity Start]<=_issue<=SetB[Validity end]  ---step1
        TREATAS (
            { MAX ( SetA[Product code] ) },
            SetB[Product code]
        ) -- also once the above filtering is done, only consider SetA[Product code] in the current filter context----step2
    )
RETURN
    IF ( ISBLANK ( _filt ) = TRUE (), FALSE (), TRUE () )

[AlexisOlson · Accepted solution]

How about this as a calculated column?

VAR CurrProd = SetA[Product code]
VAR CurrDate = SetA[Issue Date]
VAR SetBFiltered =
    FILTER (
        SetB,
        SetB[Prodcut code] = CurrProd
            && SetB[Validity Start] <= CurrDate
            && SetB[Validity End] >= CurrDate
    )
RETURN
    IF ( ISEMPTY ( SetBFiltered ), "No", "Yes" )

[Neha94]

This worked. Thanks a lot. I am new to power bi and it didnt occur to me to use a filter instead of fetching the validity date with lookup.