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
kindly give any solutions pls
Share some data and show the expected result.
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.
Cant able to upload the excel file here
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.
|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|
@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
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.