How to calculate closest earlier date in DAX on the same column as the compared date

Question

I am using Power BI and DAX.

I want to calculate the closest invoice date that is earlier than the current invoice date for a given company. My expected result would look like this, while I currently have the first 3 colums, the 4th is what I want to calculate.

 +-----------+-----------+------------------+-----------------------+   
 | invoiceId | CompanyId |   InvoiceDate    | Closest Earlier Date  |  
 +-----------+-----------+------------------+-----------------------+     
 |    103111 |    219660 | 2018-04-30 00:00 | null                  |  
 |    555056 |    219662 | 2019-02-12 00:00 | null                  |  
 |    347454 |    219668 | 2018-09-28 00:00 | 2018-08-31 00:00      |  
 |    402587 |    219668 | 2018-10-31 00:00 | 2018-09-28 00:00      |  
 |    311889 |    219668 | 2018-08-31 00:00 | 2018-06-20 00:00      |  
 |    179054 |    219668 | 2018-06-20 00:00 | 2018-06-12 00:00      |  
 |    169563 |    219668 | 2018-06-12 00:00 | null                  |  
 |    167041 |    319670 | 2018-06-08 00:00 | 2018-06-01 00:00      |  
 |    572585 |    319670 | 2019-02-21 00:00 | 2018-06-01 00:00      |  
 |    144796 |    319670 | 2018-06-01 00:00 | null                  |  
 |    103274 |    319671 | 2018-04-30 00:00 | null                  |  
 |    210641 |    319671 | 2018-07-06 00:00 | 2018-04-30 00:00      |  
 +-----------+-----------+------------------+-----------------------+

My current formula looks like this :

ClosestEarlierInvoiceDate = 
CALCULATE(
    MIN('Invoices'[InvoiceDate]);

    FILTER('Invoices';EARLIEST('Invoices'[InvoiceDate]))
)

But I know I am not filtering for the same companyId, but when I tried, i was told I had a circular reference, which I understand. But still, I don't know how to implement it.
So the query above actually return me the earliest date of the whole table, and it is the same value on each row.

Thanks for the help,

Fraligatorus Fraligatorus · Accepted Answer · 2019-03-18T15:13:21

Ok, so I ended up solving my own question. I've been trying to for like a day and a half, but now I asked it on Stack, I resolved it 45 minutes later...

I created a duplicate table, and created this calculated column in the first table :

ClosestEarlierInvoiceDate = 
CALCULATE(
    MAX('Invoices-Dates'[date]);

    FILTER('Invoices-Dates';'Invoices-Dates'[InvoiceDate] < 'Invoices'[InvoiceDate] &&
    'Invoices-Dates'[CompanyId] = 'Invoices'[CompanyId])
)

How to calculate closest earlier date in DAX on the same column as the compared date

1 Answers