Hi experts,
I'd like to count rebills only when they occur after 24 hours of previous purchase.
I have data of this format:
DateTime | Month | Transaction ID | Item Code | Cash Amount | Sales Amount | Customer ID
[Month] is simply a calculated column: Month([DateTime])
I also have a Pivot Table with [Item Code] and [Month] on the row as I would like to check rebill rate of each merchandise for each month. I'm OK with the small flaw such that a purchase occurs at the first day of a month may be classified as a rebill while the last purchase occurred less than 24 hours ago.
My thought:
Since I want to ignore any [Transaction ID] made by the same [Customer ID] within 24 hours of this row (Using COUNTX() to add a row context), something similar to the following should work, in pseudo code:
Measure :=CALCULATE(COUNTX(Sales, Sales[Customer ID]),
FILTER(Sales, Sales[DateTime] > Sales[DateTime] of this row plus 24 hours))
However because I cannot use EARLIER() to fetch Sales[DateTime] of this row, I'm kind of loss here. Would appreciate any help, and especially if you can share a general way to reference the current row in a measure.
