Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
hohlick
MVP

Measure depends on its previous results - possible?

‎12-21-2016 11:14 AM

Hi All,

I try to create a model for my logistics dept.  There is logic behind:

1. I have initial stock [IS] amount and daily sales plan [SP], also as Desired Stock Amount [DS]

2. I place an [Order] with size [DS] - ([IS] - [SP]) = [DS] - [IS] + [SP]

3. When order arrives, I calculate [Fact Stock]  = [IS] - [Fact Sales] + previous [Order].

 

To calculate next order, I have to use [Fact Stock] instead of [IS], but it depends on previous [Order] value.

 

Is it possible to calculate this model with measures? 

I can do it in Excel with referencing prev. cells, but in DAX I cannot find a solution.

 

Maxim Zelensky
excel-inside.pro
Labels:
Message 1 of 16
4,520 Views
0
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User
In response to hohlick

‎12-28-2016 03:58 AM

@hohlick

Hi Maxim,

 

Here is a dummy example of how this sort of calculation can be done.

PBIX dummy example

 

The logic is to first calculate Cumulative Sales/Orders. The differences between daily Cumulative Orders are then daily Orders, and Stock Level = Initial Stock - Cumulative Sales + Cumulative Orders.

 

I followed this sequence of calculation through a series of measures:

  1. [Fact Sales] = Sum of Sales from Sales table
  2. [Cumulative Sales] = Cumulative sum of Fact Sales
  3. [Latest Desired Stock] = Desired stock from Desired Stock table
  4. [Initial Stock (first day)] = constant value from Initial Stock table
  5. [Cumulative Orders Placed] = [Latest Desired Stock] - ( [Initial Stock (first day)] - [Cumulative Sales])
  6. [Cumulative Orders Placed 1 day ago] = [Cumulative Orders Placed] on previous day
  7. [Cumulative Orders Placed 2 days ago] = [Cumulative Orders Placed 1 day ago] on previous day
  8. [Orders Placed] = [Cumulative Orders Placed] - [Cumulative Orders Placed 1 day ago]
  9. [Orders Received] = [Cumulative Orders Placed 1 day ago] - [Cumulative Orders Placed 2 days ago]
  10. [Cumulative Change in Stock] = (-1)*[Cumulative Sales] + [Cumulative Orders Placed 1 day ago]
  11. [Fact Stock Closing] = [Initial Stock (first day)] + [Cumulative Change in Stock]

Hopefully this can be adapted to your exact situation.

 

Notes:

  • Orders are placed based on Desired Stock, but there is 1 day lead time so Desired Stock may not be attained (I think that was your situation)
  • Steps 7-9 are not necessary for stock calculation, just for information purposes.
  • Orders can be either positive or negative to get to Desired Stock (potentially unrealistic)
  • If Orders are instead option-like (only made if stock is below Desired Stock), then you can change Cumulative Orders Placed to be the largest order that would have been required so far to get to Desired Stock, i.e. MAX( MAXX ( <All dates so far> , [Latest Desired Stock] - ( [Initial Stock (first day)] - [Cumulative Sales]), 0)
  • Boundary conditions & grand totals probably need tidying up

 

Cheers,

Owen

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

Message 7 of 16
5,363 Views
15 REPLIES 15

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All