Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Display previous date per ID in power query

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 IDDate
101-01-2021
101-02-2021
105-02-2021
231-01-2021
205-02-2021
307-02-2021

 

And my desired result:

Customer IDDatePrevious Date
101-01-2021 
101-02-202101-01-2021
105-02-2021 01-02-2021
231-01-2021 
205-02-2021 31-01-2021
307-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!

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.