Last day of sale before a specific date

[TeJai]

Hey,

I am struggling writing a specific formula for finding the last day of sale before i specific date - and not just the last sales date, as i wish to track "idle" inventory development over time.

I have two tables, one with transactions and one with end of month inventory value. I need to write a new column in the financial stock value tabel which specifies the latest day of sale for a product prior to the end of month inventory date (date column in financial stock value tabel).

The dates should be extracted from the transaction tabel, date column (for sales entries), and written in the financial stock value tabel, last sale day column. Color coding shows expected output. 

Excel tables attached below as well. 

Any help is appreciated as this challenge is beyond my DAX knowledge. 

Transaction table
Product	Date	Type	value
A	14-05-2023	Sales	2
A	16-05-2023	Purchase	-2
A	27-06-2023	Sales	2
B	16-05-2023	Sales	1
B	16-06-2023	Sales	1
B	27-07-2023	Sales	1
C	14-05-2023	Purchase	-3
C	16-05-2023	Sales	3
C	27-06-2023	Sales	3

Financial Stock value tabel
Product	Date	Stock value	Last sale day	Days since last sale
A	31-05-2023	100	14-05-2023	17
B	30-06-2023	120	27-06-2023	3
C	31-07-2023	90	27-06-2023	34
A	31-05-2023	100	16-05-2023	15
B	30-06-2023	120	16-06-2023	14
C	31-07-2023	90	27-07-2023	4
A	31-05-2023	100	16-05-2023	15
B	30-06-2023	120	27-06-2023	3
C	31-07-2023	90	27-06-2023	34

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Last sale date = CALCULATE(MAX('Transaction'[Date]),FILTER('Transaction','Transaction'[Product]=EARLIER('Financial stock value'[Product ])&&'Transaction'[Type]="Sales"&&'Transaction'[Date]<=EARLIER('Financial stock value'[Date])))

Hope this helps.

[TeJai]

Hi Ashish_Mathur,

Thanks, simple solution working perfectly - and helped me figure out why my approach based on ISONORAFTER wasn't fit for this issue. 

Sincerely,

[Ashish_Mathur]

You are welcome.