Measure to confirm date in one table is between dates in another table

[jmeza1]

Hello,

I have two tables, Certification and Award. Neither have unique values. 

Certification has the following columns: 

Address Number

Alpha Name

Effective Date

Expiration Date

Certification

Award has the following columns:

Address Number

Order Number

Award Date

I am trying to show a column in Award that filters for the Address Number in Certification and confirms that the Award Date is between the Effective Date and the Expiration Date. Whether this is done as a true/false statement or a lookup of the Certification is fine. I think I need a measure but I can only get as far as confirming the Address Number in Award exists in Certification but cannot figure out the date compare. Please assist! Thanks

[MFelix]

Hi @jmeza1,

Taking into account that both your tables don't have unique values how do you know to wich of the Address number you are refering and that the result is what you want?

If you have adress number 1 with two certifications starting and ending on the dates below:

Start: 01/01/2018 End: 30/06/2018

Start: 04/09/2018 End: 20/10/2018

What result would you want for the date of 30/04/2018 just true because it enters on the first date or do you want to return the certification number?

Just want to clarify better what you need.

can you provide sample data and expected result also.

Regards,

MFelix

[jmeza1]

Here is an example of certification

Address Number	Certification	Effective Date	Expiration Date
1111	SSBE	4/19/2013	7/31/2019
1116	FWBE	2/1/2010	6/20/2013
1116	SSBE	12/28/2008	12/31/2009
1116	SSBE	2/2/2010	2/29/2012
1129	SMBE	1/1/2011	9/9/2015
1129	SSBE	8/15/2002	7/31/2004
1129	SSBE	2/21/2008	7/31/2009
1129	SSBE	4/15/2010	3/31/2012
1129	SSBE	8/3/2004	8/31/2006
1129	SSBE	9/1/1995	8/31/1997
1129	SWBE	9/1/2011	9/9/2015

Here is an example of Award

Address Number	Order Number	Contract Amount	Award Date
1111	21276030	3060	7/31/2018
1111	22254010	0	7/1/2018
1111	21254001	800	1/6/2016
1111	21254001	-7.5	7/1/2016
1111	21276030	3275	5/18/2017
1111	22254010	48000	5/1/2017
1116	214069	2287	11/27/2012
1116	214069	-12721	2/21/2013
1116	214069	153937.95	7/12/2012

Looking at your response, I guess the need is (if this is possible) is this:  for every address number in Award confirm the Award Date falls in between the Effective Date and Ending Date of the first matching Address Number in Certification. If the Award Date does not fall in between those dates then move to the next match Address Number and so forth until there is a match. When there is a match, return the certification value. If there are no matches, return nothing (or zero). 

[v-yulgu-msft · Accepted solution]

Hi @jmeza1,

Create a calculated table.

Cross Join Table =
VAR temp =
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN (
                SELECTCOLUMNS (
                    Award,
                    "Address Number", Award[Address Number],
                    "Award Date", Award[Award Date]
                ),
                SELECTCOLUMNS (
                    certification,
                    "Address Number2", certification[Address Number],
                    "Effective Date", certification[Effective Date],
                    "Expiration Date", certification[Expiration Date]
                )
            ),
            [Address Number] = [Address Number2]
        ),
        "Check", IF (
            [Award Date] >= [Effective Date]
                && [Award Date] <= [Expiration Date],
            1,
            0
        )
    )
RETURN
    GROUPBY (
        temp,
        [Address Number],
        [Award Date],
        "check2", SUMX ( CURRENTGROUP (), [Check] )
    )

Add a calculated column in Award table.

Column =
IF (
    LOOKUPVALUE (
        'Cross Join Table'[check2],
        'Cross Join Table'[Address Number], Award[Address Number],
        'Cross Join Table'[Award Date], Award[Award Date]
    )
        > 0,
    TRUE (),
    FALSE ()
)

Best regards,

Yuliana Gu

[jmeza1]

Looks like it is working! Thanks!