cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
danielpcamara Frequent Visitor
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

Accepted Solutions
danielpcamara Frequent Visitor
Frequent Visitor

Re: DAX Formula - Calculate Stock Cost - how to use EARLIER

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.

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX Formula - Calculate Stock Cost - how to use EARLIER

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 other members find it more quickly.
danielpcamara Frequent Visitor
Frequent Visitor

Re: DAX Formula - Calculate Stock Cost - how to use EARLIER

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.