Cumulative Buy and Sell

[aryaps]

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

working

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

Cumulative Buy =

VAR MyItem = ITEMLEDGERENTRY[Item_No]

VAR Entry_No = ITEMLEDGERENTRY[Entry_No]

VAR myDate = ITEMLEDGERENTRY[Posting_Date]

VAR myFilter = FILTER(

             ITEMLEDGERENTRY,

            ITEMLEDGERENTRY[Item_No] = MyItem &&  

            ITEMLEDGERENTRY[Entry_No] <= Entry_No &&  

            (ITEMLEDGERENTRY[Entry_Type] = "Positive Adjmt.") &&

            ITEMLEDGERENTRY[Posting_Date] <= myDate

            )

RETURN

    SUMX(

        myFilter  ,

        ITEMLEDGERENTRY[Quantity]

        )

[aryaps]

kindly give any solutions pls

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[aryaps]

Hello Ashish 

once you free, pls have a look into this

[aryaps]

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

[Ashish_Mathur]

Hi,

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.

[aryaps]

Cant able to upload the excel file here

[aryaps]

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 ; radacad.com/dax-inventory-or-stock-valuation-using-fifo

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.

Output

Item No.

Item Description

Remaining Quantity

0-30Days

Value

31Days-60Days

Value

61Days-90Days

Value

91Days-180Days

Value

181Days-270Days

Value

271Days-360Days

Value

365 & Above

Value

EPVCDBS

EPVCDBS

5,133.00

0

0

5,133.00

-331,789.59

0

0

0

0

[amitchandak]

@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: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

You can also consider new window function

  Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[aryaps]

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