Power BI Dax issue with running total measure that repeats total if date is repeated

[cy-user]

I have been searching for a solution to this seemingly simple request but to no avail.

I am trying to get a running total per item that never resets.  My measure fails to give the specific total when there is more than one order for an item on the same day, it just repeats the total for the item for the day.

In my example, I am using a running total measure and a Visual Calculation.

I am only showing two items: 10100112 and 10100565.

I would love help with EITHER calcualtion. A Visual Calculation would be best because it will be faster, especially once I add all items to the report.  The benefits of using the RUNNINGSUM function if I could figure that out is that it seems to recalculate the running totals if you change the sort order of the date.

The 'mRTQtyremain' calculation is:

mRTQtyremain =

VAR varDate = SELECTEDVALUE('DateTable'[Date])

VAR varItem = SELECTEDVALUE(Sales_Order_Line_Excel[No])

RETURN

    CALCULATE(

        [mQtyRemain],

        ALLSELECTED(Sales_Order_Line_Excel),

        'DateTable'[Date] <= varDate,

        Sales_Order_Line_Excel[No] = varItem

    )

The Visual Calculation is: (Not using RUNNINGSUM, but would like to)

Visual Calculation = SUMX(WINDOW(1, ABS, 0, REL, ALL([Date],[Item No],[mQtyRemain]), ORDERBY([Date]), PARTITIONBY([Item No])),[mQtyRemain])

This is where they both go wrong for what I need:

Here is some sample data you can put into a blank query in the editor:

Location_Code	Date	Item No	Order_No	Qty_Remain
MAIN	3/14/2025	10100565	S-ORD18349	10
MAIN	2/14/2025	10100565	S-ORD18348	15
MAIN	1/15/2025	10100565	S-ORD18359	1
MAIN	1/14/2025	10100565	S-ORD18347	15
MAIN	1/13/2025	10100112	S-ORD18338	20
MAIN	1/13/2025	10100112	S-ORD18343	5
MAIN	1/13/2025	10100565	S-ORD18343	5
MAIN	1/9/2025	10100112	S-ORD18318	20
MAIN	1/8/2025	10100565	S-ORD1277	1
MAIN	1/8/2025	10100565	S-ORD18309	1
MAIN	1/3/2025	10100112	S-ORD18272	35
MAIN	12/19/2024	10100565	S-ORD18214	5
MAIN	11/18/2024	10100112	S-ORD18027	15
MAIN	11/14/2024	10100112	S-ORD18005	3
MAIN	11/11/2024	10100112	S-ORD17306	4
MAIN	11/4/2024	10100565	S-ORD17414	2
MAIN	11/1/2024	10100565	S-ORD17465	1
MAIN	10/23/2024	10100565	S-ORD17300	7
MAIN	10/23/2024	10100565	S-ORD17301	7
MAIN	10/23/2024	10100565	S-ORD17303	4
MAIN	10/21/2024	10100565	S-ORD17869	2
MAIN	9/30/2024	10100565	S-ORD1263	1
MAIN	9/30/2024	10100565	S-ORD17754	4
MAIN	9/30/2024	10100565	S-ORD17758	1
MAIN	9/19/2024	10100565	S-ORD17694	1
MAIN	9/17/2024	10100565	S-ORD17675	1
MAIN	9/13/2024	10100565	S-ORD17563	5
MAIN	9/13/2024	10100565	S-ORD17665	1
MAIN	9/11/2024	10100112	S-ORD17629	35
MAIN	9/11/2024	10100565	S-ORD17634	2
MAIN	9/3/2024	10100565	S-ORD17537	1
MAIN	8/20/2024	10100112	S-ORD17359	2
MAIN	8/15/2024	10100112	S-ORD17305	2
MAIN	8/14/2024	10100112	S-ORD17281	1
MAIN	8/9/2024	10100112	S-ORD17227	1
MAIN	8/9/2024	10100565	S-ORD17230	2
MAIN	7/11/2024	10100112	S-ORD16910	50

Thank you in advance for your time.

[cpride · Accepted solution]

This issue was resolved with the help of @jeroenterheerdt .

This WINDOW function worked for my requirements, which I used as a Visual Calcualtion:

Visual Calculation = SUMX(WINDOW(1, ABS, 0, REL, ALLSELECTED([Date],[Item No],[Order_No]), ORDERBY([Date]), PARTITIONBY([Item No])),[Qty_Remain])

[jeroenterheerdt]

glad we got it working and thanks for your feedback!

[Anonymous]

Hi @cy-user 

The calculation issue you encountered might be due to a blank 'Item No'. If possible, please fill in the blank 'Item No'.

Best Regards,

Jayleny

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

[cy-user]

I have attached the data and also shared a link to the sample report.  There are only 37 rows in this example and no blanks.

Can you please see if you can tell what I am doing wrong?