Forum Discussion
Distinct count over time with conditions
Hi,
I'm looking for some help with a DAX expression.
I'm trying to create a measure that can count the number of unique Items on hand at any given date in time. My fact table represents an inventory transaction by date and item - if an item gets sold or purchased, there would be a corresponding transaction in this table with the +/- quantity. In my model, these are related to the date dimension and the item master dimension.
I currently have a method for calculating the running balance of Inventory quantity.
InventoryBalance:=
CALCULATE (
SUM(Inventory[Quantity]),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date]
<= MAX ( 'Calendar'[Date] )
)
)To create the distinct count measure, I know there's two considerations to make. First, not every item will have a transaction in the fact table for every day. An item can sit for months without activity, therefore it's necessary to look at ALL items. Second, since an item can sit then it's necessary to calculate the running inventory balance for each item and prior date in the context.
I tried this measure, but performance is terrible and it only returns items that had a transaction in the timerange.
Measure 2:= var varTable = SUMMARIZE( 'Item', 'Item'[ItemCode], "On Hand", Inventory[InventoryBalance] ) return COUNTX( FILTER( varTable, [On Hand] > 0 ) , [On Hand] )
The end goal is to plot the number of Items on hand on a line graph and view by month over several years.
If it matters, I'm creating this in Visual Studio/SSAS for consumption in Power BI.
Hello sitm-matt
Give this a try:
Items With Inventory Balance = COUNTROWS( FILTER ( VALUES ( 'Item'[ItemCode] ), [InventoryBalance] > 0 ) )
4 Replies
- jdbuchanan71
Super User
Hello sitm-matt
Assuming your Inventory table is joined to your Date table, something like this should work.
Measure 2 = CALCULATE ( DISTINCTCOUNT ( 'Item'[ItemCode], Inventory )
If you pull the Month-Year from the date table the above will count the unique ItemCode that exist in the Inventory in a given Month-Year.
In Contoso this is what the count of product that exist in sales looks like which is along the lines of what you are looking for yes?
Hi jdbuchanan71
I'm afraid the dataset is a little more complicated than that. The Inventory table does not contain balances, only transactions.
Here's an example of the Inventory table.
When I calculate the InventoryBalance measure, here's the output.
Item B doesn't have transactions in Feb-April, but it has an Inventory Balance so I want it counted. Item C has a transaction in February, but it doesn't have Inventory Balance so it shouldn't be counted in that month.
Here's the output of your suggested measure.
Here's what I'm hoping to get output.
- jdbuchanan71
Hello sitm-matt
Give this a try:
Items With Inventory Balance = COUNTROWS( FILTER ( VALUES ( 'Item'[ItemCode] ), [InventoryBalance] > 0 ) )
