Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
julesdude
Post Partisan
Post Partisan

Count Total Number of Assets Purchased but not Sold

Hi,

I have a table in my data model like this:

Asset Reference  Transaction Type  Transaction Date
AAA010Purchase15-Dec-16
AAA009Purchase15-Dec-16
AAA009Sale22-Feb-22
AAA008Purchase15-Dec-16
AAA007Purchase15-Dec-16
AAA006Purchase15-Dec-16
AAA006Sale28-Apr-22
AAA005Purchase15-Dec-16
AAA003Purchase15-Dec-16
AAA002Purchase15-Dec-16
AAA002Sale30-Jul-21
AAA001Purchase15-Dec-16

I would like to get a grand total in a card visual of assets that have been purchased but not sold, as per [as of date] which is a date picker the user selects in the report.

So, for example, if the [as of date] = 31/12/2022, from the above there should be a total of 6 assets currently purchased but not sold.

I'm thinking this might need the sum of a filtered count that searches for 'Purchase' or 'Sale' for each asset. But there might be a more efficient way of doing this. Any help appreciated.

1 ACCEPTED SOLUTION

Yes, that should work. Try

Num unsold items =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( 'Table'[Asset Reference] ),
        "@purchased",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Transaction Date] <= ReferenceDate,
                'Table'[Transaction type] = "Purchase"
            ),
        "@sold",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Transaction Date] <= ReferenceDate,
                'Table'[Transaction type] = "Sale"
            )
    )
RETURN
    COUNTROWS ( FILTER ( SummaryTable, [@purchased] > [@sold] ) )

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

You could try

Num unsold items =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR PurchasedItems =
    CALCULATETABLE (
        VALUES ( 'Table'[Asset Reference] ),
        'Table'[Transaction date] <= ReferenceDate,
        'Table'[Transaction type] = "Purchase"
    )
VAR SoldItems =
    CALCULATETABLE (
        VALUES ( 'Table'[Asset Reference] ),
        'Table'[Transaction date] <= ReferenceDate,
        'Table'[Transaction type] = "Sale"
    )
VAR UnsoldItems =
    EXCEPT ( PurchasedItems, SoldItems )
RETURN
    COUNTROWS ( UnsoldItems )

This should work if an asset can only be bought and sold once. If an asset can be purchased then sold and then repurchased then different code would be needed.

Thank you @johnt75 

You are right - your solution does work for 99% of assets, but there is the possibilty that an asset could be purchased, sold and then purchased again, and I think there is one case where this is so. Even if not, there could potentially be a case of this in the future.
So I was thinking of some form of count for each asset of 'purchase' transactions, and a count of 'sale' transactions and if purchase is greater than sale then it can be counted as +1.
I am not sure the most efficient way to express it in the DAX though or if that is the best logical way of achieving the goal?

Yes, that should work. Try

Num unsold items =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( 'Table'[Asset Reference] ),
        "@purchased",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Transaction Date] <= ReferenceDate,
                'Table'[Transaction type] = "Purchase"
            ),
        "@sold",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Transaction Date] <= ReferenceDate,
                'Table'[Transaction type] = "Sale"
            )
    )
RETURN
    COUNTROWS ( FILTER ( SummaryTable, [@purchased] > [@sold] ) )

It works perfectly. Thank you for your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors