Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aryaps
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

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]
        )

 

9 REPLIES 9
aryaps
Frequent Visitor

kindly give any solutions pls

Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

agg.jpg

 

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 ; 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 DescriptionRemaining Quantity0-30DaysValue31Days-60DaysValue61Days-90DaysValue91Days-180DaysValue181Days-270DaysValue271Days-360DaysValue365 & AboveValue
EPVCDBSEPVCDBS5,133.00005,133.00-331,789.590000      

 

amitchandak
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: 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

 

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.