Search for missing dates and values

[Anonymous]

Hello! 

For exampleHow can I get the value from previous dates, if my column has more dates than other table?

[iamprajot]

not sure what is it that you need from what is shown here.

[Anonymous]

Hello!

I need a column, where calculate 'revenue' by date, and key. If date is blank, I need previous value (revenue).

Thanx!

[iamprajot · Accepted solution]

Here it is,

I have named these 2 tables as A and B

First create a calculated column in the first Table shown in the Picture

DateKEY = A[Date]&A[KEY]

and then create another calculated column to get the revenue

Result=LOOKUPVALUE(A[Revenue],A[DateKEY],IF(ISBLANK(LOOKUPVALUE(A[Date],A[DateKEY],B[Date]&B[KEY])),CALCULATE(MAX(A[Date]),FILTER(A,A[Date]<EARLIER(B[Date]))),LOOKUPVALUE(A[Date],A[DateKEY],B[Date]&B[KEY]))&B[KEY])