Counting the number of times a sale has happened within the last 12 months from the date of sale

[WhoCares535454]

Hi there. Dax is being annoying.

I have a large table containing the ID of sales, the date of the sale, a list of product IDs, and a customer name. Something like this:

Sale ID	Date	Product ID	Customer
1	07/02/2020	a	Harry
2	29/04/2021	b	Harry
3	04/10/2020	a	Dave
4	25/01/2021	a	Harry
5	31/12/2021	b	Dave
6	07/10/2021	b	Dave
7	12/01/2021	b	Harry
8	20/05/2020	a	Harry
9	01/12/2021	a	Harry
10	11/04/2021	b	Dave
11	25/12/2021	a	Dave

What I want is a new column. For each sale, I want to know how many of this product we've sold to harry in the year before it. In other words, For each sale ID, it will calculate the number of sales we've had:

- within the previous 12 months of the sale date

- to Harry (we don't care about Dave, those rows can just be blank)

- of the same product ID

So for example for sale ID 4, it would return the number of sales of product ID A, between 25/01/2020 and 25/01/2021, which in this case is only 1 - Sale ID 1, which took place on 07/02/2020 (English date formatting)

Currently my code looks something like this:

countofsales = var saledate =Table1[Date]
return
calculate(
countrows(
filter(
table1, table1[date]<=saledate)),
allexcept(table1, table1[sale id])

This works nicely to count the number of sales over all time, but I can't figure out a way to count the sales from only the 12 months previous to the current sale. Can anyone help?

[v-yalanwu-msft · Accepted solution]

Hi, @WhoCares535454 ;

You could create a column by dax.

Column = 
IF([Customer]<>"Harry",BLANK(),
CALCULATE(COUNT('Table'[Sale ID]),
          FILTER('Table', [Customer]="Harry"&&[Date]<=EARLIER('Table'[Date])&&[Date]>=EDATE(EARLIER([Date]),-12))))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.