Apply Pivot Table on selected items with addition filter of billed in current quarter only

New Contributor

1. Below is image of data set on which I have to apply pivot. For reference I gave 3 sets, actually it can go upto 5000

2. Conditions are:
a) Customer name, manufacturer & item name should be same
b) total billing should >= 3000
c) One of Billing should happen in this current quarter, like in current scenario from 1-July-22 to 31-sep-22. So it could be case that part billed is >=3000 last year, but if in current quarter even if 1 dollar is billed, then it should count

3. Attached image, Robert should appear in end result since its billed in this quarter & total value is >=3000

4. How to do that? I can apply Pivot on rest of columns, but this condition that there should be some billing in this quarter, how to do that?

 

 

Customer NameManufacturerItemBill dateAmount
RobertNewwayApple15-Jul-221500
JassHighendOrange10-Dec-214000
RobertNewwayApple07-Jan-201500
     
     
     
End Result    
Customer NameManufacturerItemBill dateAmount
RobertNewwayApple15-Jul-221500
RobertNewwayApple07-Jan-201500
4 Replies
Could you please attach the dataset with a few more records.. kindly attach the excel file
Regards, Faraz

@vt_excel Not sure that a pivot table is what you need. I created something in Power Query that seems to achieve the desired output, based on the conditions you mentioned. See if you can get it to work on your real data.

 

Hi Riny_van_Eekelen
How you have used power query and what filer/formula applied? I will check at my excel too.

@vt_excel Well, when you open the Advanced Editor in Power Query, you can see the applied steps that take you from the blue table to the green one. It's not very straight-forward if you haven't worked with PQ before. Have you?