Summarize or Group By with multiple conditions?

[spencer_II]

So I've "noodled" this for a little too long and am probably overlooking a simple solution.

I have a single table that has product ID, transaction date and expiration date by LOT numbers. Plus transfer qty and on hand.

example:

The objective is to identify above condition.  For item 101550 there was product expiring on 12/26/2024 but transfers

of 210 Cases were made against 4/5/2025 expiring product. The earliest expiring product should be transfered first!!!

After reviewing "manually" using above table these are really outlier issues but tracking and count summaries of these conditions are being reqeusted.

Any help is and always appreciated.

Spencer_II

[v-jianpeng-msft]

Hi, @spencer_II 

If you want to group or summarize your data, you can use the SUMMARIZE function. In this function, you can select the column you want to group or summarize. You can also use the calculate and filter functions together to implement multi-condition filtering. data and group them. I created the following sample data:

I created a new table and used the SUMMARIZE function to summarize the above data and filter the data with the smallest Exp dt:

Table 2 =
CALCULATETABLE (
    SUMMARIZE (
        'Table',
        'Table'[item number],
        'Table'[Lot number],
        "Date", CALCULATE ( MIN ( 'Table'[Exp dt] ), FILTER ( 'Table', 'Table'[Exp dt] ) )
    )
)

In your description, for 101550 items, I think it may be because Trs dt is empty, so the corresponding behavior of sum of Trans qty bU/M is empty. You can override a measure to calculate Trans qty bU/M instead of using the default sum aggregation function directly. If you can make a sample pbix file or data that does not contain private data, it will help you solve the problem faster. You can click the link below to learn how to use summarize:

SUMMARIZE - DAX Guide

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[spencer_II]

Thank you for the replies... This has led me in a few solid directions for solutions.  Right now calculated columns to the rescue using a combination of RANKX and OFFSET to complete the necessary conditions to filter on. I'm providing a sample table with some data.  This table is the result of a power query merge of two tables (one for inventory/lot expiration date & a sales table for withdrawal date.

Itp	Whs	Item number	Name	Lot number	Exp dt	Trs dt	Transfer Qty	On Hand Approve
Z21	B28	101548	6/51 OZ	5024960	12/28/2024	1/11/2024	-4000	3916
Z21	B28	101548	6/51 OZ	5024960	12/28/2024	1/12/2024	-3200	7832
Z21	B28	101548	6/51 OZ	5025286	12/29/2024	null	null	1500
Z21	B28	101548	6/51 OZ	5025821	3/28/2025	null	null	2200
Z21	B28	101549	12/16.9 OZ	5024961	12/22/2024	1/5/2024	-4256	4161
Z21	B28	101549	12/16.9 OZ	5024961	12/22/2024	1/11/2024	-4256	4161
Z21	B28	101549	12/16.9 OZ	5024961	12/22/2024	1/12/2024	-1824	8322
Z21	B28	101549	12/16.9 OZ	5025786	3/21/2025	1/12/2024	-190	1311
Z21	B28	101550	12/33.8 OZ	5025194	12/26/2024	null	null	2576
Z21	B28	101550	12/33.8 OZ	5025747	4/5/2025	1/11/2024	-210	798
Z21	B28	101550	12/33.8 OZ	5025747	4/5/2025	1/12/2024	-420	1596
Z10	B28	1074184	3/2x3 LTR	14459	6/4/2025	null	null	40
Z10	B28	1074184	3/2x3 LTR	18333	1/10/2026	1/17/2024	-1760	48
Z10	B28	1074184	3/2x3 LTR	18339	1/11/2026	1/18/2024	-11200	2415

Thanks again for the review and assistance.

[Greg_Deckler]

@spencer_II Right, so would need much more information to be able to provide a solution for this but I have done similar stuff and it's fun stuff to work on. If you can provide more information I might be able to take a look. 

Order Fulfillment - Microsoft Fabric Community

Delivery Dates - Microsoft Fabric Community

Love the supply chain stuff. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.