Writing DAX in calculated field

0
votes

I have 2 tables:

A. Customer, dimension table, with customer key as primary key. Key fields include:

  • Customer key,
  • 1stPurchaseDate (Date)

B. Sales, fact table, with customer key as foreign key. Key fields include:

  • Sales order date (Date),
  • Customer key

I want to create a DAX to know the count of sales order per 1st purchase month (like Jul 2017), with sales(customer key) = customer (customer key), sales order date within 60 days since first purchase.

I can create it in calculated column in below:

=CALCULATE(COUNT(Sales[ProductKey]),FILTER(Sales,Sales[OrderDate]>Customers[DateFirstPurchase] && Sales[OrderDate] - Customers[DateFirstPurchase] <=60 && Sales[CustomerKey] = Customers[CustomerKey]))

But how can I do it in DAX (calculated field)?

Thanks a lot for your help!!

1
powerbipowerpivot

1 Answers

0
votes

First, create a relationship in your model (if you use PowerPivot see an article from this site:)

https://support.microsoft.com/en-us/office/relationships-between-tables-in-a-data-model-533dc2b6-9288-4363-9538-8ea6e469112b

Second, modify your statement and use SELECTEDVALUE

CALCULATE(COUNT(Sales[ProductKey]),FILTER(Sales, SELECTEDVALUE(Sales[OrderDate])>Customers[DateFirstPurchase] &&  SELECTEDVALUE(Sales[OrderDate]) <= (Customers[DateFirstPurchase] + 60) ) )