Forum Discussion
srl01
Helper II
Helper IISearch within previous 90 days
Hello all - a question regarding searching through transaction data. I have a retail transaction data table - columns for date, customer ID, item quantity, and total revenue. It also has a flag for ...
Answering this with how I got it working eventually, in case it can help others:
IsExactCancellation=
if(Transactions[IsNormalTransaction]=1,BLANK(),
CALCULATE(lastdate(Transactions[Date]),FILTER(all(Transactions),
Transactions[IsNormalTransaction]=1 && Transactions[date]>=earlier(Transactions[Date])-90
&& Transactions[date]<=earlier(Transactions[Date])
&& Transactions[CodeForExactCancellationCheck]=earlier(Transactions[CodeForExactCancellationCheck]))))With a CodeForExactCancellationCheck column generated by concatenating the various item numbers and quantities in the basket to make it a unique view of the basket content in one (long) number.
srl01Helper II
Helper IIAnswering this with how I got it working eventually, in case it can help others:
IsExactCancellation=
if(Transactions[IsNormalTransaction]=1,BLANK(),
CALCULATE(lastdate(Transactions[Date]),FILTER(all(Transactions),
Transactions[IsNormalTransaction]=1 && Transactions[date]>=earlier(Transactions[Date])-90
&& Transactions[date]<=earlier(Transactions[Date])
&& Transactions[CodeForExactCancellationCheck]=earlier(Transactions[CodeForExactCancellationCheck]))))
With a CodeForExactCancellationCheck column generated by concatenating the various item numbers and quantities in the basket to make it a unique view of the basket content in one (long) number.