Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I have a table in my data model like this:
Asset Reference | Transaction Type | Transaction Date |
AAA010 | Purchase | 15-Dec-16 |
AAA009 | Purchase | 15-Dec-16 |
AAA009 | Sale | 22-Feb-22 |
AAA008 | Purchase | 15-Dec-16 |
AAA007 | Purchase | 15-Dec-16 |
AAA006 | Purchase | 15-Dec-16 |
AAA006 | Sale | 28-Apr-22 |
AAA005 | Purchase | 15-Dec-16 |
AAA003 | Purchase | 15-Dec-16 |
AAA002 | Purchase | 15-Dec-16 |
AAA002 | Sale | 30-Jul-21 |
AAA001 | Purchase | 15-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.
Solved! Go to 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] ) )
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
28 | |
23 | |
12 | |
11 | |
10 |