Value of closest prior date - 2 tables

[AmaliaD]

Hello,

I am reaching out for another DAX predicament I am facing. I have been navigating forums and sites for several days, but I cannot find the solution I am seeking. 

The scenario:

1-there are 2 tables

2-in Table1 (which is the fact table), there is a date (same date can appear multiple times)

3-in Table 2, I also have a date column and a value of interest that I need to bring in Table1 (date can appear only once)

The need:

-to take the value from Table2 and bring it in Table1, based on date

The complication:

-the value that needs to be brought is not of the same date, but that of the prior day (example - in fact Table1, for 1st of April 2023, I need to bring the value that is shown in Table2 for 31st of March 2023). This I can do and have 2 formulas for it

v1=CALCULATE(MIN(Table2[Value of interest]),FILTER(ALLEXCEPT(Table2,Table2[Value of interest]),Table1[Date]-1=Table2[Date]))

v2=LOOKUP(Table2[Value of interest], Table2[Date], Table1[Date]-1)

The issue:

-Table2 may not have the day calculated with -1, so the formula needs to look for the value from the prior closest date it finds in Table 2 

Example: Table 1 has 1st of April => the formula should look for 31st of March, but, if Table2 does not have 31st of March, it needs to take 30th or 29th (the value of the prior immediate date)

It is here where I am facing the trouble. 

Has anyone had any similar situations and found a solution?

Many thank for any help!

Amalia 

[amitchandak · Accepted solution]

@AmaliaD , Try like

New column =

Maxx(filter(Table2, Table2[Value of interest] = Table1[Value of interest]  &&Table2[Date] <= Table1[Date] ), Table2[Date])

or split into column or var one for = and one for <=

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[AmaliaD]

Hi Amit,

Thank you so much for the help. 

I made it in two steps:

1-a variation of the above maxx formula 

=Maxx(Filter(Table2,Table2[Date]<Table1[Date)],Table2[Date] )   -> this, to be able to see the actual prior date it brings out

2-a simple Lookup, using the date column calculated as above

The [Value of interest] column appears only in Table2, so I don't have what to compare it to from Table1 (i just need to bring the appropriate one there).

Once again, very grateful for the help!

Amalia