Display previous date per ID in power query

[Anonymous]

Hi,

Similar to this thread for DAX, I would like to add a column in Power Query that calculates the previous date of a sale per customer ID.

Example data:

Customer ID	Date
1	01-01-2021
1	01-02-2021
1	05-02-2021
2	31-01-2021
2	05-02-2021
3	07-02-2021

And my desired result:

Customer ID	Date	Previous Date
1	01-01-2021
1	01-02-2021	01-01-2021
1	05-02-2021	01-02-2021
2	31-01-2021
2	05-02-2021	31-01-2021
3	07-02-2021

Since I have milions of rows, a calculated column in DAX is not preferred since it slows down the pbix a lot.

Does anyone know how to do this in power query?

Thanks!

[poweryogesh · Accepted solution]

@Anonymous 
This thread might solve your issue
Previous Date using M query  

[Anonymous]

Thanks @poweryogesh . The function that @ImkeF wrote did the trick.

[poweryogesh]

@Anonymous 
Glad to see the thread worked for you 

[amitchandak]

@Anonymous , both power query and Dax is going to take load time.

Both will be calculated columns 

min = Maxx(filter(Table, [Customer ID] = earlier([Customer ID] ) && [Date]  < earlier([Date])  ), [Date] ) 

I think no of steps in power query vs DAX, it should take same load time 

[Anonymous]

Hi @amitchandak ,

Thanks you for your reply. My Power BI file has become very slow due to calculated columns, it takes minutes for a measure to appear when I press on 'new measure'.

In this thread I've read that this is caused by having too many calculated columns. Therefore, I want to push my calculations back to Power Query. Loading time of the queries is not a problem, but having to wait minutes for every filter/measure I simply press in desktop is not workable.

[amitchandak]

@Anonymous , both power query and Dax is going to take load time.

Both will be calculated columns 

min = Maxx(filter(Table, [Customer ID] = earlier([Customer ID] ) && [Date]  < earlier([Date])  ), [Date] ) 

I think no of steps in power query vs DAX, it should take same load time