Forum Discussion
Distinct count over time with conditions
Hello sitm-matt
Give this a try:
Items With Inventory Balance = COUNTROWS( FILTER ( VALUES ( 'Item'[ItemCode] ), [InventoryBalance] > 0 ) )
Super UserHello 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 ) )
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.