Count Occurence based on Product ID and Date Range

[Anonymous]

I want to create a calculated column that would look at the Product ID of that row, look at the Order date, and, looking at the entire list, count the number of product IDs that are greater than the lookback date and less than the order date...

I've tried countX, sum, nothing seems to get me what I would need on a per row basis...

Any ideas?

Product ID	Order Date	Lookback
D	30-Jun	23-Jun
D	1-Jul	24-Jun
D	30-Jun	23-Jun
D	4-Jul	27-Jun
D	9-Jul	2-Jul
A	2-Jul	25-Jun
A	4-Jul	27-Jun
D	6-Jul	29-Jun
D	1-Jul	24-Jun
D	9-Jul	2-Jul
D	4-Jul	27-Jun
D	30-Jun	23-Jun
D	7-Jul	30-Jun
D	30-Jun	23-Jun
D	6-Jul	29-Jun
D	7-Jul	30-Jun
B	7-Jul	30-Jun
D	1-Jul	24-Jun
A	4-Jul	27-Jun
D	9-Jul	2-Jul
B	1-Jul	24-Jun
B	1-Jul	24-Jun
A	1-Jul	24-Jun
A	1-Jul	24-Jun
D	4-Jul	27-Jun
B	1-Jul	24-Jun
D	9-Jul	2-Jul
B	Jun-31	25-May
D	30-Jun	23-Jun
B	30-Jun	23-Jun
D	30-Jun	23-Jun
D	1-Jul	24-Jun
D	1-Jul	24-Jun
B	6-Jul	29-Jun

[PaulDBrown]

Try

ID count =
VAR _OrderDate =
    MAX ( Table[Order Date] )
VAR _LookBackDate =
    MAX ( Table[Lookback] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table[ID] ),
        FILTER (
            ALL ( 'Table' ),
            Table[Order Date] < _OrderDate
                && Table[Lookback] > _LookBackDate
        )
    )

[ddpl]

@Anonymous ,...if I'm understood your concern correctly then following Calculated Column might be your solution...

Your Need =
COUNTROWS (
     FILTER (
          'Table',
          'Table'[Product ID] = EARLIER ( 'Table'[Product ID] )
          && 'Table'[Order Date] <= EARLIER ( 'Table'[Order Date] )
          && 'Table'[Order Date] >= EARLIER ( 'Table'[Lookback] )
     )
)

[ddpl]

@Anonymous 

Could you please share the sample solution with required column in above database.

[Anonymous]

Maybe this will help....

Product ID	ID Order	Date Look Back	Expected count
a	24-Jun	17-Jun	0
a	27-Jun	20-Jun	2
a	2-Jul	25-Jun	3
a	4-Jul	27-Jun	3
a	4-Jul	27-Jun	4
b	29-Jun	22-Jun	0
b	30-Jun	23-Jun	1
b	1-Jul	24-Jun	3
b	1-Jul	24-Jun	4
b	1-Jul	24-Jun	5
b	6-Jul	29-Jun	6
b	7-Jul	30-Jun	6
d	30-Jun	23-Jun	0
d	30-Jun	23-Jun	2
d	30-Jun	23-Jun	3
d	30-Jun	23-Jun	4
d	30-Jun	23-Jun	5
d	30-Jun	23-Jun	6
d	1-Jul	24-Jun	7
d	1-Jul	24-Jun	8
d	1-Jul	24-Jun	9
d	1-Jul	24-Jun	10
d	1-Jul	24-Jun	11
d	4-Jul	27-Jun	12
d	4-Jul	27-Jun	13
d	4-Jul	27-Jun	14
d	6-Jul	29-Jun	15
d	6-Jul	29-Jun	16
d	7-Jul	30-Jun	17
d	7-Jul	30-Jun	18
d	9-Jul	2-Jul	7
d	9-Jul	2-Jul	8
d	9-Jul	2-Jul	9
d	9-Jul	2-Jul	10

[Anonymous]

Hi @Anonymous ,

Could you show me more details about your calculation? I am confused about how to get expected count.

Product ID	ID Order	Date Look Back	Expected count
a	24-Jun	17-Jun	0
a	27-Jun	20-Jun	2

Do you want to count the Product ID which is greate than look back date and less than Order date?

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Rico...

So, yes, what I need to do, per row, I'm looking at the product ID, and the order date (there's actually a date/time for the Order Date, that might make it easier to understand)... 

I'm taking the order date/time, and subtracting 7 from the date, so I can say "within the past seven days (including this transaction), how many times did this product get ordered.

For the first entry, Product A was ordered on 6/24, the "Lookback date" is 7 days back, so I start looking anything from 6/17 through 6/24..  And count how many Product A's were ordered.

Since I have no orders within that time frame for Product A, that would be 1 (now that I think about it since I have to count that order with it)...

Next row, there's a Product A ordered on 6/27, lookback date is 6/20...

Counting all of the Product A orders between 6/20 - 6/27, that should give me 2..

etc etc... 

Hope that makes sense...