Find value in table 2 when ID and Date(if possible or closest older date) match between 2 tables

[alya1]

Hi All,

Not sure if this is possible but I have 2 tables like below...

table 1:

ID	Date
1	1/1/11
1	1/2/11
2	1/1/11
2	1/2/11
3	1/1/11
3	1/2/11

table 2:

ID	Date	Hours
1	1/1/11	1
1	1/2/11	2
2	1/1/11	3
2	1/5/11	4
3	12/21/10	5
3	1/1/11	6

I would like to add a column in table 1 by using value (Hours) from table 2 that matches ID exactly AND Date if possible. If no dates match, then use value (Hours) from closest prior date.  
This is tricky because some ID don't have matching dates such as ID 2: 1/1/11 match but there is no 1/2/11 in table 2 so I would like to use the closest date going backwards of 1/1/11. For ID 3: no dates match so I would like to use the closest date going backwards for both cases. 

Goal table

ID	Date	Hours
1	1/1/11	1
1	1/2/11	2
2	1/1/11	3
2	1/2/11	3
3	1/1/11	5
3	1/2/11	6

Thank you!

[ahadkarimi · Accepted solution]

Hi @alya1, load your two tables, and using the measure below, you can achieve this result, if you encounter any issues, let me know.
Downnload PBIX file

Hours = 
VAR currentID = 'Table 1'[ID]
VAR currentDate = 'Table 1'[Date]
VAR closestDate =
    CALCULATE(
        MAX('Table 2'[Date]),
        FILTER(
            ALL('Table 2'),
            'Table 2'[ID] = currentID && 'Table 2'[Date] <= currentDate
        )
    )
VAR resultHours = 
    CALCULATE(
        MAX('Table 2'[Hours]),
        FILTER(
            ALL('Table 2'),
            'Table 2'[ID] = currentID && 'Table 2'[Date] = closestDate
        )
    )
RETURN
    resultHours

Did I answer your question?  If so, please mark my post as the solution!✔️
Your Kudos are much appreciated!  Proud to be a Responsive Resident!

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = LOOKUPVALUE(Table2[Hours],Table2[Date],CALCULATE(max(Table2[Date]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Date]<=EARLIER(Table1[Date]))),Table2[ID],Table1[ID])

Hope this helps.

[ahadkarimi · Accepted solution]

Hi @alya1, load your two tables, and using the measure below, you can achieve this result, if you encounter any issues, let me know.
Downnload PBIX file

Hours = 
VAR currentID = 'Table 1'[ID]
VAR currentDate = 'Table 1'[Date]
VAR closestDate =
    CALCULATE(
        MAX('Table 2'[Date]),
        FILTER(
            ALL('Table 2'),
            'Table 2'[ID] = currentID && 'Table 2'[Date] <= currentDate
        )
    )
VAR resultHours = 
    CALCULATE(
        MAX('Table 2'[Hours]),
        FILTER(
            ALL('Table 2'),
            'Table 2'[ID] = currentID && 'Table 2'[Date] = closestDate
        )
    )
RETURN
    resultHours

Did I answer your question?  If so, please mark my post as the solution!✔️
Your Kudos are much appreciated!  Proud to be a Responsive Resident!

[DataNinja777]

Hi @alya1 ,

You can generate your desired output by creating a calculated column using the following DAX formula:

Column (Filled) = 
VAR CurrentID = 'Table 1'[ID]
VAR CurrentDate = 'Table 1'[Date]
VAR ClosestPreviousHours =
    CALCULATE(
        MAX('Table 2'[Hours]),
        FILTER(
            'Table 2',
            'Table 2'[ID] = CurrentID &&
            'Table 2'[Date] <= CurrentDate
        )
    )
RETURN
    IF(
        ISBLANK('Table 1'[Column]),
        ClosestPreviousHours,
        'Table 1'[Column]
    )

The [Column] is generated from the standard lookupvalue dax formula, and the DAX formula above is applied to create the [Column (Filled)] next to it.

I have attached an example pbix file for your reference.