Distinct count over time with conditions

[sitm-matt]

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.

[jdbuchanan71]

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?

[sitm-matt]

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 · Accepted solution]

Hello @sitm-matt 

Give this a try:

Items With Inventory Balance = 
COUNTROWS( FILTER ( VALUES ( 'Item'[ItemCode] ), [InventoryBalance] > 0 ) )

[sitm-matt]

@jdbuchanan71  Thank you very much! I was definitely over complicating it in my mind. Your measure works flawlessly! Performance is good over a large dataset too.