Last Sale Amount- LASTNONBLANK()

[lync0056]

Hi, 

I'm trying to calculate the last Sale Amount in a Sales Table. (See screenshot of my model below) My measure is as follows;

Last Sale Amount = CALCULATE( [Total Sales] , LASTNONBLANK( Dates[Date] , [Total Sales] ) )

The moment I make the Date and Invoice tables bi-directional it works. But then breaks alot of my other measures with below screenshot errors. 

Any help would be great.

Thank you

Cameron

[v-jianhe-msft]

Hi,

What is the relationship between Sales table and Invoice table? If you set it a single cross filter, how is the result? 

[lync0056]

It is not possible to create a direct relationship between the Invoices table and Sales Table. Indirectly there is a pointer from Sales to Journals and then Journals to Invoices. 

[lync0056 · Accepted solution]

I received the solution to this on a different forum. Pasted below.

Last Sales Amount  =
VAR LastSalesDate = MAX( Invoices[Date] )

RETURN
CALCULATE( [Total Sales] ,
FILTER( ALL( Dates ),
Dates[Date] = LastSalesDate ))