cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amikhan12345
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
ImkeF
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
amikhan12345
Frequent Visitor

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

ImkeF
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

amitchandak
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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.