Forum Discussion
Adding a sequential number based on the date
Hi everyone
I'm having a problem adding a sequential number column to my Power BI report and wondered if someone could please help?
Our CRM system hold various donations, each of which have multiple transactions with different dates. I want to add a column into my report (but not in my CRM!) that shows the transaction number from 1 to however many per donation and based on date sorted in order. I was hoping that an Index Column might work, but it doesn't seem to.
So, on the image below, Donation DON0078557 has multiple Transactions sorted in increasing date order. I would therefore like TRAN15467 to show as 1 in the new column, TRAN15468 to show as 2 and so on. This would need to be repated (starting again at 1) for each Donation:
Is there a way to achieve this in Power BI?
Many thanks
Jon
jonclay , a New column in Dax
Countx(filter( Table, [donation] =earlier([donation]) && [Date] <= earlier([Date]) ), [Date])
7 Replies
jonclay , a New column in Dax
Countx(filter( Table, [donation] =earlier([donation]) && [Date] <= earlier([Date]) ), [Date])
Hi there Amit,
Many thanks for your quick reply. I've tried this and I get an error saying "Token Literal expected" on the [Date] after the &&. Do you know why this might be?
Many thanks
Jonjonclay , Can you share what you created. Suggested one seem correct to me
I've just discovered that this doesn't work in Direct Query Mode (which I was in). I changed the Mode and this now works. Thank you!
