Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Cumulative Buy and Sell

Hello Experts

am trying to make a new column for the Cumulative sum of purchase and Sell for the report. The [Cumulative Buy] , [Previous Cumulative Buy] and [Cumulative Sell] ,[Previous Cumulative Sell] columnscolumns take care of the running totals . 

the issue am facing is 

1. Need to add/filter Entry type is - Purchase / Negative Adjustment/Postive adjustment

2. this query is giving only gives the Result if only one purchase is happened. if multiple is happend on same day it is not


3. how we can get this 4 columns based on the quantity and entry type


Cumulative Buy =
VAR myDate = ITEMLEDGERENTRY[Posting_Date]
VAR myFilter = FILTER(
            ITEMLEDGERENTRY[Item_No] = MyItem &&  
            ITEMLEDGERENTRY[Entry_No] <= Entry_No &&  
            (ITEMLEDGERENTRY[Entry_Type] = "Positive Adjmt.") &&
            ITEMLEDGERENTRY[Posting_Date] <= myDate
        myFilter  ,


Frequent Visitor

kindly give any solutions pls

Super User
Super User


Share some data and show the expected result.

Ashish Mathur

Hello Ashish 

once you free, pls have a look into this

i want to create the same last 4 columns. the issue is
a) the same day multiple buy and multple sale will occur then values is not getting proper

b)  type = buy, positive adjustment (For Purchase)
     type=  Sale, negative ajustment (For sale transactions)

c) eg: if type is purchase/postive ajustment then in  sale colum should come the previous value as per the below diagrm


Note: data source is from live business central so i think we cant index also for this. so how do we crack this


request all experts solutions for this







I still cannot understand.  Share an MS Excel workbook with your formulas written there.  I will try to convert  those Excel formulas into DAX formulas.

Ashish Mathur

Cant able to upload the excel file here

Hello Ashish


 trying to make the aging Report based on the aging slot of 0-30, 31-60,61-90,91-120,121-180,181-270,271-365,365 and above plus value also on each slot. (FIFO Method). Aging Date is Posting Date


This is the link of partial solution of our  requirement ;


Multiple Sale,Purchase, Negative, Positive Adjustment may come on the same Day also.


Entry types for Sale                :  Sale, Negative Adjustments

Entry types for Purchase        : Purchase , Positive Adjustments.



Item No.Item DescriptionRemaining Quantity0-30DaysValue31Days-60DaysValue61Days-90DaysValue91Days-180DaysValue181Days-270DaysValue271Days-360DaysValue365 & AboveValue


Super User
Super User

@aryaps , better to join your date of the table with the date of date table and then create cumulative


Then would be simple measure like

CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))


Running Total/ Cumulative:



You can also consider new window function

  Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD:


!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

thanks for the reply

could you explain in detail. bcz am new to the bi and dax.

the issue am facing is when we have multiple sale or purchase in occured in same day then the issue is coming

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors