Filter table to last date in the past and first date in the future

[samtheham]

Hello all, 

I have a table with a series of order dates and volumes by Company and Item. The orders contain both past and future orders, and I would like to get the most recent past order and the closest incoming future order for each Company/Item combination. The issue I am running up against is that I have a large quantity of data (~1m items across ~150 companies), and am constantly running up against query limits. I am building this query in report builder, and have tried "chunking" my query by item (I have built an R script that can automatically generate a number of reports), but am running into runtime issues. My data is proprietary, but see below for an example of how it is structured:

The resulting data can be structured either long or wide, I do not care.

I have tried a number of different solutions to this. The main issue results from the fact that I also need the order volume, so I can't just calculate the maximum and minimum dates because I also need to grab their corresponding volume. Does anyone have some suggestions of a *computationally efficient* solution to my issue? 

Thanks very much!

[123abc]

To efficiently filter your data to find the most recent past order and the closest incoming future order for each Company/Item combination while also retaining their corresponding volumes, you can use the following approach in Power Query within Power BI or Excel:

Assuming your table is named "Orders" and has columns "Company," "Item," "OrderDate," and "Volume," here's a step-by-step guide:

1. Sort the Table: Sort the table by "Company," "Item," and "OrderDate" in ascending order.

2. Add Custom Columns: Add two custom columns:

   • "PastOrder": This column will contain the most recent past order for each Company/Item combination.
   • "FutureOrder": This column will contain the closest incoming future order for each Company/Item combination.

   You can use the following formulas:

let Source = YourSource, // Replace with your actual data source Sorted = Table.Sort(Source,{{"Company", Order.Ascending},{"Item", Order.Ascending},{"OrderDate", Order.Ascending}}), Grouped = Table.Group(Sorted, {"Company", "Item"}, { {"PastOrder", each Table.LastN(_, 1), type table}, {"FutureOrder", each Table.FirstN(_, 1), type table} }), Expanded = Table.ExpandTableColumn(Grouped, "PastOrder", {"OrderDate", "Volume"}, {"PastOrder.OrderDate", "PastOrder.Volume"}), Expanded2 = Table.ExpandTableColumn(Expanded, "FutureOrder", {"OrderDate", "Volume"}, {"FutureOrder.OrderDate", "FutureOrder.Volume"}) in Expanded2

1. Filter the Dates: After adding the custom columns, you can filter the data to keep only the rows where "OrderDate" matches the "PastOrder.OrderDate" or "FutureOrder.OrderDate" columns, respectively.

2. Remove Unnecessary Columns: Once you have filtered the data to include only the past and future orders, you can remove any unnecessary columns to keep the table clean.

This approach efficiently groups the data by Company and Item, identifies the past and future orders for each combination, and retains their corresponding volumes. It should work well with a large dataset without running into query limits.

Remember to replace "YourSource" with the actual name of your data source in the Power Query code, and adjust column names as needed based on your actual data structure.

[samtheham]

Hi, thanks for taking the time to reply! As a caveat: I am still pretty new to the PowerBI software. My organization has our data stored in PowerBI Datasets, so I only have experience so far pulling data via DAX commands. The code you've supplied looks like Power Query... do you know how I would put this command in Report Builder? I think it's expecting DAX code.

[123abc]

I understand your situation. If you're working with Power BI Datasets and Report Builder, you will need to achieve your desired outcome using DAX as Power Query transformations are typically performed during data loading, and Report Builder primarily works with DAX expressions.

Here's a high-level approach using DAX in Power BI Report Builder:

1. Create a New Table: In Power BI Desktop, go to the "Model" view, and in the "Data" pane, right-click on your dataset and select "New Table."

2. Define a DAX Measure: Create a DAX measure that calculates the past and future order dates for each Company/Item combination. You can use the following DAX measures as an example:

PastOrderDate = VAR CurrentDate = TODAY() RETURN CALCULATE( MAX('YourTableName'[OrderDate]), FILTER( 'YourTableName', 'YourTableName'[OrderDate] <= CurrentDate ) ) FutureOrderDate = VAR CurrentDate = TODAY() RETURN CALCULATE( MIN('YourTableName'[OrderDate]), FILTER( 'YourTableName', 'YourTableName'[OrderDate] > CurrentDate ) )

1. Replace 'YourTableName' with the actual name of your dataset table.

2. Use the DAX Measures: In Report Builder, you can use these DAX measures in tables, matrices, or visuals to display the past and future order dates along with other relevant data.

3. Filter Data: You can then apply filters to your visuals or tables to show only the rows where the date matches the calculated past or future order date.

Please note that while Power Query is typically used for data preparation and transformation, DAX is used for calculations and aggregations within Power BI visuals and reports. In your case, you'll use DAX to create calculated measures that dynamically calculate the past and future order dates based on the current date.

Make sure to adjust the DAX expressions and table names according to your dataset and report requirements.

[samtheham]

Cheers, my friend. I'll try this later today at work when I get a chance, and mark it as a solution if it works!