Adding a sequential number based on the date

[jonclay]

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]

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!

[amitchandak · Accepted solution]

@jonclay , a New column in Dax

Countx(filter( Table, [donation] =earlier([donation]) && [Date] <= earlier([Date]) ), [Date])

[jonclay]

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
Jon

[amitchandak]

@jonclay , Can you share what you created.  Suggested one seem correct to me

[jonclay]

Hi Amit

Thank you. Please see the image below (using the exact column names rather than date etc):

Many thanks
Jon

[amitchandak]

@jonclay , That is DAX, not power query formula

for power query refer if this can help

https://www.youtube.com/watch?v=7CqXdSEN2k4

[jonclay]

@amitchandak Ah yes, of course! However, when I try to create a new Column in the Modeling tab and then enter the DAX, it doesn't seem to recogise the 'earlier' word.