Running total with dates within a separate date category

[lopazopy]

I'm trying to find a running total for orders packed within a certain PromiseDate "bucket". The dates may be in any order in my table, but the way I'm thinking they are ordered by PromiseDate in descending order and PackDate in ascending order within the PromiseDate. Looking at the records with a PromiseDate of 6/10/2023 with the expected running total will give an idea of what I'm working with.

Also, something else I've come across is if orders have the same pack date it groups those together, so I guess something else like OrderNum sort has to be taken into consideration. OrderNum is unique.

I've searched a lot and see there are many topics on this subject, but I haven't been able to get this to work (RANKX, Quick Measure - Running Total, DAX Calculate, etc). Thank you in advanced for any help or insight.

PromiseDate	OrderNum	PackDate	PackLbs	ExpectedRunning
6/17/2023	1485	4/28/2023	1000	1000
6/10/2023	3847	5/2/2023	2000	3000
6/10/2023	4120	5/1/2023	1000	1000
6/10/2023	2587	5/4/2023	2000	5000
6/3/2023	2285	4/29/2023	1000	1000
6/3/2023	5874	4/29/2023	3000	4000
5/27/2023	5668	4/30/2023	5000	5000

[Jihwan_Kim · Accepted solution]

Hi,

I assume the expected outcome is a measure, not a calculated column.

Please check the below picture and the attached pbix file.

Expected outcome running total: =
IF (
    HASONEVALUE ( Data[PromiseDate] ),
    CALCULATE (
        SUM ( Data[PackLbs] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE (
                ALL ( Data ),
                Data[PromiseDate],
                Data[OrderNum],
                Data[PackDate],
                Data[PackLbs]
            ),
            ORDERBY ( Data[PackDate], ASC, Data[OrderNum], ASC ),
            ,
            PARTITIONBY ( Data[PromiseDate] )
        )
    )
)

[lopazopy]

Thanks so much! I had not seen the WINDOW function being used yet. I had to upgrade to the latest Desktop version of PowerBI for it to work.