Increase Since Last Invoice|Power Query|Excel Forum|My Online Training Hub

Sorry for not including sample data. I'm trying to add 2 columns to the query. I would to create 2 new columns (Previous Hours & Increase Since Last Invoice). So when the Total Hours columns updates, the previous total hours will be moved to the Previous Hours column and the increase in hours for each PO # will show in the Increase Since Last Invoice column. Hope this helps.

Cheers!

Thank you so much for the quick reply, Mynda. I hadn't known about this handy feature, however, is there a way to do it without the date field? I'd like to keep it so that there is only one line per PO#, with the aggregated hours and the increase after that; I could probably leave off previous hours column as that is a little redundant. 

Thanks!

Each PO (purchase order) is tied to only one project, and the total hours represent how many hours the engineer billed for technical review work related to the project; in my query I grouped the POs and selected sum for the hours column.  So although date is a factor, I'd like to leave the date field out of the table and just list each projects (PO) and how many total hours have been billed against it, and after the latest invoice has been added to the data source, the column after that will show the how many hours were billed against the PO in the most recent invoice. I hope I explained that a bit better, and again, thank you for the prompt reply.

Cheers!

I tried rearranging the pivot but I don't want the invoice date and # in the table, just PO# and the sum of total hours by PO#, and then the increase of hours per PO#; the pivot you made has difference from, which shows negative #s. I was hoping that Power Query could solve this by summing total hours grouped by PO (which I already created in my query) and then when there is a new invoice related to a PO#, show in a new column the increase in billed hours from the previous aggregated total. Hope this makes sense.

Thanks!  

Thanks Mynda. I think this is a bit more complicated than I initially thought because I'd like to see what the increase since last invoice is but not all POs are billed each invoice, so there will be many POs that have 0 while the only increase will be for the handful of POs on the most recent invoice. Attached is a mock-up of 3 POs; PO 67778 was billed for the most recent invoice on 3/27/18, and the other 2 POs were not. Essentially, the end result I'd like to show in the query are the total hours invoiced to date, and the increase from last invoice. The increase is useful because we don't want to see the engineers exceed an 8 hour threshold per PO per invoice. I'm thinking that the Previous Invoice Hours column is redundant and difficult to calculate because the most recent invoice date varies depending on the PO, so I guess it makes sense to not base any of the calculations off of the date, as this not a constant variable. Perhaps it makes more sense to just show Total Hours invoices and the most recent increase, and if null, then to show 0.

Tykru

Sorry I thought I already replied. This is perfect. Thanks Mynda!