Forum Discussion
Unique lastest trasaction
Hi All
I'm trying to return a column that gives me the latest used ('Transaction Date') 'Reporting entity' by 'Project ID', however I'm having an issue when a 'Project ID' has two latest transactions on the same day using multiple 'Reporting Entities'. In this case I only want to see one reporting entity though it doesn't matter which one.
In the example above the 'Latest Transaction?' column is just using an IF statement to determine if the 'Latest Transaction Date' matches the 'Transaction Date' but you can see it states 'yes' for two project ID's that are the same where I would only want it to say 'yes' to one row per unique project ID.
Is this possible at all?
Thank you
Chris
Hi birdie29,
In your scenario, you can add a index column in Query Editor like below:
Then back to the report, create a column to rank duplicate rows for each Project ID based on the index column, then create another column to return "Yes" for each Project ID when rank vale is 1.
Rank = RANKX(
FILTER(
ALLSELECTED(Table1),Table1[Project ID]=EARLIER(Table1[Project ID])
),
'Table1'[Index],
,1)Latest Transaction? = IF('Table1'[Transaction Date]='Table1'[Latest Transaction Date] && 'Table1'[Rank]=1,"Yes","")
Best Regards,
Qiuyun Yu
2 Replies
Hi birdie29,
In your scenario, you can add a index column in Query Editor like below:
Then back to the report, create a column to rank duplicate rows for each Project ID based on the index column, then create another column to return "Yes" for each Project ID when rank vale is 1.
Rank = RANKX(
FILTER(
ALLSELECTED(Table1),Table1[Project ID]=EARLIER(Table1[Project ID])
),
'Table1'[Index],
,1)Latest Transaction? = IF('Table1'[Transaction Date]='Table1'[Latest Transaction Date] && 'Table1'[Rank]=1,"Yes","")
Best Regards,
Qiuyun YuAssuming that the Project ID always falls in pairs next to eachother (like your example shows), try adding and Index column starting with zero and then add the following custom column:
try if Table1[ID]{[Index]}=Table1[ID]{[Index]-1} and [Latest Trans Date]=[Trans Date] and Table1[Latest Trans Date]{[Index]}=Table1[Latest Trans Date]{[Index]-1} then "Yes" else "" otherwise ""
