• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / Increase Since Last Invoice|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryIncrease Since Last Invoice
sp_PrintTopic sp_TopicIcon
Increase Since Last Invoice
Page: 12Jump to page
Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
1
April 23, 2019 - 7:03 am
sp_Permalink sp_Print

Hi All,

I've created a query that shows the total hours worked on a project for each invoice. I want to add 2 new columns: one will be previous total hours worked from last invoice; the second column will be the increase from last invoice to current invoice. I'm sure it's stating me in the face but am stuck. Any thought?

 

Thanks!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
April 23, 2019 - 9:00 am
sp_Permalink sp_Print

Hi Tykru,

Can you please upload a sample Excel file with your data? Be sure to connect the query to data in the Excel file, not an external source.

Thanks,

Mynda

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
3
April 24, 2019 - 12:16 pm
sp_Permalink sp_Print

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!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
April 24, 2019 - 3:56 pm
sp_Permalink sp_Print

Hi Tykru,

Thanks for sharing your file. You don't need Power Query for this. You can do it with a PivotTable > Show Values As > Difference From.

See file attached.

Mynda

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
5
April 24, 2019 - 10:24 pm
sp_Permalink sp_Print

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!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
April 25, 2019 - 10:20 am
sp_Permalink sp_Print

Hi Tykru,

Your question isn't clear. How do you tell what the last invoice was? I assumed it's based on date, but it's not clear if this data is for one customer or multiple customers. The PO numbers are not consistent i.e. one has a letter in it, so I assumed this data relates to multiple customers.

That said, you can change the fields in the Row labels of the PivotTable yourself to get the desired result. You'll need to reapply the Show Values As calculation for the new fields.

Mynda

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
7
April 25, 2019 - 10:02 pm
sp_Permalink sp_Print

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!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
April 26, 2019 - 9:53 am
sp_Permalink sp_Print

Ok, but did you have a go at rearranging the PivotTable yourself to get your desired result? If you got stuck, please upload your file again so I can see where you went wrong and point you in the right direction.

Mynda

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
9
May 5, 2019 - 2:07 am
sp_Permalink sp_Print

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!  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
May 6, 2019 - 10:33 am
sp_Permalink sp_Print sp_EditHistory

Hi Tykru,

Can you please provide a mock-up of what you'd like to see in Excel. It doesn't have to include all of the data, just a few of the PO#s. It needs to reconcile to the data you have provided so I can trace it back.

Mynda

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
11
May 6, 2019 - 11:42 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
12
May 7, 2019 - 9:05 pm
sp_Permalink sp_Print

Hi Tykru,

Please see Excel file attached. You can do this with Power Query by extracting the various pieces of data and then merging the queries back together.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
13
June 4, 2019 - 3:22 am
sp_Permalink sp_Print

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

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
14
September 3, 2019 - 12:21 am
sp_Permalink sp_Print

Mynda Treacy said
Hi Tykru,

Please see Excel file attached. You can do this with Power Query by extracting the various pieces of data and then merging the queries back together.

Mynda  

Mynda,

 

I haven't had a chance to parse one of the queries in your attached solution until now, but can you please clarify the below code for the query named Last Invoice?

= Table.SelectRows(Source, let latest = List.Max(Source[Invoice Date]) in each [Invoice Date] = latest)

The name of the step is Filtered Rows; I know you can use the query editor to filter but it seems there's no filter on any of the columns.

 

Thanks!

Tyler

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
15
September 3, 2019 - 10:16 am
sp_Permalink sp_Print

Hi Tyler,

I just used the filter buttons. You'll see there is a filter on the Last Invoice query Invoice Date column.

Mynda

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
16
September 4, 2019 - 12:06 am
sp_Permalink sp_Print

Invoice-Date_No-Filter-Icon.pngImage Enlarger

No worries, I think I know what it is: when I opened the query I got a compatibility warning; it seems I might be using an older version of Excel or Power Query, I thought mine is up-to-date. Attached is a shot of the query; there's no filtered icon on the Invoice Date column.

 

Cheers!

sp_PlupAttachments Attachments
  • sp_PlupImage Invoice-Date_No-Filter-Icon.png (12 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
17
September 4, 2019 - 8:26 am
sp_Permalink sp_Print

The filter icon is the drop down button on the column header. If you click on that you will have more filter options.

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
18
September 4, 2019 - 10:09 pm
sp_Permalink sp_Print

Yep, I  know. What I meant to say previously is I think the reason I couldn't tell that you filtered the column using the drop down button is because there was no filter icon on it after you filtered it; and maybe the reason it wasn't showing has to do with the compatibility warning. I've seen similar scenarios in the past when certain features in the editor don't show up after encountering the warning.

 

Thanks Mynda 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
19
September 5, 2019 - 2:41 pm
sp_Permalink sp_Print

Oh, I see 🙂

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
20
September 5, 2019 - 9:57 pm
sp_Permalink sp_Print

No worries. By the way, I'd just like to compliment you on your content and all the work you do, it is simply awesome. 

Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Anders Sehlstedt, RAQUEL ACION, Dana Friedt, Chandler Davis, gogy13, Brenda Richards, Mark Luke
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 202
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
Forum Stats:
Groups: 3
Forums: 24
Topics: 6218
Posts: 27267

 

Member Stats:
Guest Posters: 49
Members: 31901
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.