DAX Formula - Calculate Stock Cost - how to use EARLIER

[danielpcamara]

Hello Community,

I have a data set that look like this: 

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)

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?

[v-frfei-msft]

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.

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

[danielpcamara · 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.