cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielpcamara
Frequent Visitor

DAX Formula - Calculate Stock Cost - how to use EARLIER

Hello Community,

I have a data set that look like this: 
Sem título.png

It is a Product x Month x Value of purchase x Amount of purchase x Amount of sells.
I need to build a Monthly report that has Product x initial balance + new purchases - sells = final balance.

I builded a report using some formulas to calculate previous sales and purchases, to get initial balance. But the problem is every month the value pay in the product is changing. So with my current formulas I'm getting this:

 

(here you see many months and one product to be easy to track changes)

Sem título.png

 

As you can see the value of Final Balance 07/2016 is not the same as the Inicial Balance of 08/2016.

 

What I need is to Calculate the Initial Balance using the information on the previous month of this particular product. I want to my formula to be something like this:

Initial Balance:=CALCULATE([Final Balance];
               FILTER (ALLEXCEPT ( 'Stock Base';'Stock Base'[ID_PRODUCT]);
                  'Stock Base'[MES] < DATEADD('Stock Base'[Date];-1;MONTH)))

But a notification of circular reference is show to me since my Final Balance Formula is:

Final Balance:= [Initial Balance] + [Purches] - Sells

I think that the formula EARLIER is the answer, but don't know how. 

 

Can someone help me?

1 ACCEPTED SOLUTION

Thanks @v-frfei-msft.

 

I did a little bit more research and I get these formulas that Solved my problem:

 

 

Ammount of Purchase:=SUM('Base Stock'[Purchase Quantity])
Value of Purchase:=SUM('Base Stock'[Purchase Total])
Ammount of Sells:=SUM('Base Stock'[Purchase Quantity])

Value of Sells

 

Value of Sells:=DIVIDE(CALCULATE ( [Value of Purchase];
    FILTER (ALLEXCEPT ( 'Base Stock';'Base Stock'[IDPRODUCT]); 'Base Stock'[Date] <= MAX('Base Stock'[Date])));
    CALCULATE ( [Ammount of Purchase];
    FILTER (ALLEXCEPT ( 'Base Stock';'Base Stock'[IDPRODUCT]); 'Base Stock'[Date] <= MAX('Base Stock'[Date])));0) * [Ammount of Sells]

Then I add these calculated columns:

 

 

Ammount of IB:=CALCULATE([Ammount of Purchase]-[Ammount of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>'Base Stock'[Date]))
Value of IB:=CALCULATE([Value of Purchase]-[Value of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>'Base Stock'[Date]))
Ammount of FB:=CALCULATE([Ammount of Purchase]-[Ammount of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>='Base Stock'[Date]))
Value of FB:=CALCULATE([Value of Purchase]-[Value of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>='Base Stock'[Date]))

 

 

Thanks any way.

 

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @danielpcamara,

 

Actually the EARLIER function could not be used in measure. Here I made one sample for your reference. 

 

Firtsly, create a calculated column.

 

col = Table1[Amont of purchase]*Table1[values of purchase] - Table1[Amont of sells]*Table1[values of sells]

Then we can create measures as below.

 

final balance = CALCULATE(SUM(Table1[col]),FILTER(ALL(Table1),Table1[date]<=MAX(Table1[date])))
Initial Balance = CALCULATE([final balance],DATEADD(Table1[date],-1,MONTH))

Then we can get the result as we excepted.

 

Capture.PNG

 

For more details, please check the pbix as attached. If it doesnt meet your requirement, kindly share your sample data to me more specifically.

 

https://www.dropbox.com/s/wzfgbopuiqr78bd/DAX%20Formula.pbix?dl=0

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Thanks @v-frfei-msft.

 

I did a little bit more research and I get these formulas that Solved my problem:

 

 

Ammount of Purchase:=SUM('Base Stock'[Purchase Quantity])
Value of Purchase:=SUM('Base Stock'[Purchase Total])
Ammount of Sells:=SUM('Base Stock'[Purchase Quantity])

Value of Sells

 

Value of Sells:=DIVIDE(CALCULATE ( [Value of Purchase];
    FILTER (ALLEXCEPT ( 'Base Stock';'Base Stock'[IDPRODUCT]); 'Base Stock'[Date] <= MAX('Base Stock'[Date])));
    CALCULATE ( [Ammount of Purchase];
    FILTER (ALLEXCEPT ( 'Base Stock';'Base Stock'[IDPRODUCT]); 'Base Stock'[Date] <= MAX('Base Stock'[Date])));0) * [Ammount of Sells]

Then I add these calculated columns:

 

 

Ammount of IB:=CALCULATE([Ammount of Purchase]-[Ammount of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>'Base Stock'[Date]))
Value of IB:=CALCULATE([Value of Purchase]-[Value of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>'Base Stock'[Date]))
Ammount of FB:=CALCULATE([Ammount of Purchase]-[Ammount of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>='Base Stock'[Date]))
Value of FB:=CALCULATE([Value of Purchase]-[Value of Sells];
                         FILTER('Base Stock';'Base Stock'[IDPRODUCT]=EARLIER('Base Stock'[IDPRODUCT]) &&
                         EARLIER('Base Stock'[Date])>='Base Stock'[Date]))

 

 

Thanks any way.

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors