Good morning,
I am trying to figure out how many points of distribution (distinct count of customers) I have for a given product within the most recent 90 days.
I have three lookup tables:
My fact (data) table is ‘sales_data’ and it is at the invoice level. That means that a customer can have a row for each product they bought within a day and a column for how many cases were deliverd and how much they were charged.
Here is my formula.
POD Delv Cases 2019:=CALCULATE(
DISTINCTCOUNT( sales_data[SD_CUST_ID] )
, FILTER( customer_table , [Delivered Cases] >= 1 )
, FILTER( ALL( calendar_data[Date] ) , calendar_data[Date] > TODAY() – 90 )
)
Where – [Delivered Cases] = SUM( sales_data[delv_cases])
Can anyone help me understand why a product that has no delivered cases in the last 90 days would show anything other than 0?
Thank you
I am trying to figure out how many points of distribution (distinct count of customers) I have for a given product within the most recent 90 days.
I have three lookup tables:
- sku_data – unique values for all of my products (how I link my product ID)
- customer_table – unique values for all of my customers (how I link my customer ID)
- calendar_data – my date table
My fact (data) table is ‘sales_data’ and it is at the invoice level. That means that a customer can have a row for each product they bought within a day and a column for how many cases were deliverd and how much they were charged.
Here is my formula.
POD Delv Cases 2019:=CALCULATE(
DISTINCTCOUNT( sales_data[SD_CUST_ID] )
, FILTER( customer_table , [Delivered Cases] >= 1 )
, FILTER( ALL( calendar_data[Date] ) , calendar_data[Date] > TODAY() – 90 )
)
Where – [Delivered Cases] = SUM( sales_data[delv_cases])
Can anyone help me understand why a product that has no delivered cases in the last 90 days would show anything other than 0?
Thank you