cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Lookup value of previous month in the same table in Power Query

Hi,

 

https://community.powerbi.com/t5/Desktop/Lookup-for-the-value-of-previous-month-in-the-same-table/td...

 

Hope everyone is well. I am trying to achieve a solution similar to the above thread but using Power Query instead of DAX. I have appended multiple extracts of the same data in the same structure. Each extract has a timestamp column displaying the extraction date.

 

I want to create a custom column using Power Query which displays the same value but for the previous month. Example below:

 

Example.PNG

 

Best regards,

Ami

1 ACCEPTED SOLUTION
Super User II
Super User II

Hi @amikhan12345 ,

you must group on "Customer" and return all values from your table. Then a apply a function that returns the previous row value like so: Fast and easy way to reference previous or next rows in Power Query or Power BI – The BIccountant 

If you need help applying this, please paste sample data that can easily used like described here:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Frequent Visitor

Thank you @amitchandak and thank you @ImkeF for the Power Query solution. The additional video on your website on was very helpful.

Super User II
Super User II

Hi @amikhan12345 ,

you must group on "Customer" and return all values from your table. Then a apply a function that returns the previous row value like so: Fast and easy way to reference previous or next rows in Power Query or Power BI – The BIccountant 

If you need help applying this, please paste sample data that can easily used like described here:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Super User IV
Super User IV

@ImkeF , Can you help on this.

 

@amikhan12345 , check with M experts . In Dax code can be

var _max = maxx(filter(Table,[customer] =earlier([customer) && [recorded timestamp] <earlier([recorded timestamp]) ),[recorded timestamp])
return
maxx(filter(Table,[customer] =earlier([customer) && [recorded timestamp] =_max ),[status])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak 

 

I am trying to implement this formula as a DAX calculated column but the formula is not returning any fields for Customer. When you have a moment could you refine the syntax for me? (I appreciate you probably improvised this measure as an example)Example.PNG

 

Ami

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors