Forum Discussion
Summarizing data between two dates and filtered by location and invoice category
I'm looking to sum invoices between two dates and filtered by location. One location (battery) could pull in anywhere between 1 and 20 individual datasets (wells). The invoices also have different categories. I've included samples of the tables I have and an example of what I want to get out of it.
All of the data has the necessary relationships (linked by Well ID#). And I have a Date Table w/ relationship to everything else.
Well names/ID and the Battery they go to:
| Well | Well ID# | Battery |
| Well 1 | 1111 | Battery 1 |
| Well 2 | 2222 | Battery 1 |
| Well 3 | 3333 | Battery 1 |
| Well 4 | 4444 | Battery 1 |
| Well 5 | 5555 | Battery 2 |
| Well 6 | 6666 | Battery 2 |
| Well 7 | 7777 | Battery 3 |
| Well 8 | 8888 | Battery 3 |
| Well 9 | 9999 | Battery 4 |
Battery name, the start date and end dates (start date + 180 days):
| Battery | Start Date | End Date |
| Battery 1 | 1/1/2024 | 6/29/2024 |
| Battery 2 | 2/1/2024 | 7/30/2024 |
| Battery 3 | 3/1/2024 | 8/28/2024 |
| Battery 4 | 4/1/2024 | 9/28/2024 |
Invoices for each well:
| Well | Well ID# | Cost Category | Invoice Amt |
| Well 1 | 1111 | Surface | 100 |
| Well 1 | 1111 | Surface | 200 |
| Well 1 | 1111 | Downhole | 300 |
| Well 2 | 2222 | Surface | 150 |
| Well 2 | 2222 | Surface | 500 |
| Well 2 | 2222 | Downhole | 300 |
| Well 2 | 2222 | Downhole | 400 |
| Well 2 | 2222 | Downhole | 100 |
Deliverable:
| Battery | Start Date | Surface Costs | Downhole Costs |
| Battery 1 | 1/1/2024 | 300 | 300 |
| Battery 2 | 2/1/2024 | 650 | 800 |
| Battery 3 | 3/1/2024 | ||
| Battery 4 | 4/1/2024 |
Hi Anonymous ,
Based on the information you have provided, there seems to be some food for thought about your expected results.
Your Well 1 and Well 2 both correspond to Battery 1 , whereas the final desired result contains a Battery 2 classification. Anyway hopefully the following steps will help:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bckrDsAgFETRrTRoDH+w3UQFQVTgUA2mu+8VTUC8ayaZU6u6+hiHUVoZYs57zv68XE3/arktyeq4HcnquT3JGrgDbWqXRu5IsibuRJu6pZk7k6yFu9CmXrX2AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Well = _t, #"Well ID#" = _t, Battery = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Well", type text}, {"Well ID#", Int64.Type}, {"Battery", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Well"}, Well_CostCategory, {"Well"}, "Well_CostCategory", JoinKind.LeftOuter), #"Expanded Well_CostCategory" = Table.ExpandTableColumn(#"Merged Queries", "Well_CostCategory", {"Cost Category", "Invoice Amt"}, {"Well_CostCategory.Cost Category", "Well_CostCategory.Invoice Amt"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Well_CostCategory", {"Battery"}, Battery_Date, {"Battery"}, "Battery_Date", JoinKind.LeftOuter), #"Expanded Battery_Date" = Table.ExpandTableColumn(#"Merged Queries1", "Battery_Date", {"Battery", "Start Date"}, {"Battery_Date.Battery", "Battery_Date.Start Date"}) in #"Expanded Battery_Date"Fianl output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
Based on the information you have provided, there seems to be some food for thought about your expected results.
Your Well 1 and Well 2 both correspond to Battery 1 , whereas the final desired result contains a Battery 2 classification. Anyway hopefully the following steps will help:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bckrDsAgFETRrTRoDH+w3UQFQVTgUA2mu+8VTUC8ayaZU6u6+hiHUVoZYs57zv68XE3/arktyeq4HcnquT3JGrgDbWqXRu5IsibuRJu6pZk7k6yFu9CmXrX2AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Well = _t, #"Well ID#" = _t, Battery = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Well", type text}, {"Well ID#", Int64.Type}, {"Battery", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Well"}, Well_CostCategory, {"Well"}, "Well_CostCategory", JoinKind.LeftOuter), #"Expanded Well_CostCategory" = Table.ExpandTableColumn(#"Merged Queries", "Well_CostCategory", {"Cost Category", "Invoice Amt"}, {"Well_CostCategory.Cost Category", "Well_CostCategory.Invoice Amt"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Well_CostCategory", {"Battery"}, Battery_Date, {"Battery"}, "Battery_Date", JoinKind.LeftOuter), #"Expanded Battery_Date" = Table.ExpandTableColumn(#"Merged Queries1", "Battery_Date", {"Battery", "Start Date"}, {"Battery_Date.Battery", "Battery_Date.Start Date"}) in #"Expanded Battery_Date"Fianl output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.