Return 0 if no non blank values before a certain date can be found

[julesdude]

Hi

I would like to apply a filter to my matrix table in my report to restrict the rows that are displayed. I already have the following code to do this:

Asset is in Portfolio = 
VAR _asofdate = [As Of Date]
VAR _transaction = LASTNONBLANKVALUE(Asset_Transaction[Transaction Date], MIN(Asset_Transaction[Transaction Type]))
RETURN
    IF (_transaction = "Sale" ,0 ,1)

This was fine for a while - I filter if value is 1 so that if the last [Transaction Type] by [Transaction Date] was "Sale" then the asset is sold and should no longer be listed in the table.

However, I want to enhance the above so that if the As Of Date selected returns the last Transaction Type as Sale OR if no results are found for dates preceding the As Of Date, to return 1, as like Sold it is not in the portfolio.

I tried adding this:

 IF (_transaction = "Sale" || BLANK(_transaction) ,0 ,1) but it has not worked.

Any ideas?

[johnt75]

Try

Asset is in Portfolio =
VAR _asofdate = [As Of Date]
VAR _transaction =
    CALCULATETABLE (
        TOPN ( 1, Asset_Transaction, Asset_Transaction[Transaction Date], DESC ),
        Asset_Transaction[Transaction Date] < _asofdate
    )
RETURN
    IF (
        ISEMPTY ( _transaction )
            || SELECTCOLUMNS ( _transaction, "@val", [Transaction Type] ) = "Sale",
        0,
        1
    )

[julesdude]

Hi @johnt75 

Thanks. I am getting an error for this. Does [Transaction Type] need to be qualified by the table name?

[johnt75]

I think you have multiple transactions for the same asset on the same day. Is there a unique ID column which you can add to the TOPN, so that you sort first by the date and then by the unique ID so that it will act as a tie breaker ?

[julesdude]

Hi @johnt75 

The table's quite a simple one and looks like this:

Last Updated	Asset  Reference	Transaction   Type	Transaction   Date	Net Transaction   Price
05/01/2023 16:35	AAA	Purchase	25-Mar-22	273929
13/04/2022 13:27	BBB	Purchase	29-Mar-22	3727985
13/04/2022 16:59	CCC	Purchase	01-Mar-22	23110000
06/12/2022 15:10	BBB	Sale	10-Nov-22	4000000
01/04/2020 12:10	DDD	Puchase	05-May-22	459000

Last Updated is not reliable as it shows a date and time sometimes well after the Transaction Date.

It is virtually impossible that for an Asset there would be two entries both on the same Transaction Date, i.e. a Purchase and then Sold on the same day. And the only categories in Transaction Date are Purcahse or Sale, this is never blank. 

To explain - basically if an Asset Reference is on this table it must first be a Purchase in Transaction Type. If it is listed again at a later Transaction Date, it would be Sale. In theory, it could be purchased again and sold again, but that wouldn't happen often. 

Now i think about it, perhaps lastnonblank is the wrong function for this. I basically want to return a 1 or True if the asset is currently owned based on the [As Of Date] or 0/False if it is not.

[johnt75 · Accepted solution]

Easiest way to check for multiple rows with the same date is to create a measure simply COUNTROWS(Asset_Transaction) and put this in a table visual with the transaction date and the asset. Sort the table descending on the measure and that will show any assets with multiple transactions on the same date

[julesdude]

Thanks @johnt75 yes I thought COUNTROWS might be a better contender here. 

[julesdude]

@johnt75 my solutiion in the end based on your suggestion:

Asset is in Portfolio = 
VAR _asofdate = [As Of Date]
VAR _sale = 
    COUNTROWS(
        FILTER(
            Asset_Transaction,

            Asset_Transaction[Transaction Date] <= _asofdate
            && Asset_Transaction[Transaction Type] = "Sale"
        )
    )
VAR _purchased =
    COUNTROWS(
        FILTER(
            Asset_Transaction,
            Asset_Transaction[Transaction Date] <= _asofdate
            && Asset_Transaction[Transaction Type] = "Purchase" 
        )
    )
RETURN
IF( _purchased > _sale, 1, 0)

 This measure  i dropped into the visual's filter and filtered it to show items when the value is 1.

I have a similar filter issue but I will start a new thread for it. 

Thanks