- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- DAX Formula - Calculate Stock Cost - how to use EA...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

danielpcamara

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-30-2018
06:17 AM

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?

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 3

1 ACCEPTED SOLUTION

Accepted Solutions

danielpcamara

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-31-2018
03:43 AM

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

v-frfei-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-31-2018
01:07 AM

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

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.

If this post

danielpcamara

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-31-2018
03:43 AM

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.