cancel
Showing results for
Did you mean:
kschubert417 Frequent Visitor

## Net Inventory Over Time

Hi All,

From what I understand this is an incredibly simple calculation but I cannot for the life of me get it to work. What I am trying to do is calculate the net inventory given what is currently on hand, then subtract the forecast/sales orders from the inventory to get my net inventory over time.

I have the below table (tabel 1) that I merged together from the forecast, inventory, and sales order queries I made below:

table1 (Imgur)

And I am trying to get the below output:

output (Imgur)

Any and all help would be appreciated,

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Net Inventory Over Time

Looking at your table believe that the dates are mixed up so your columns for forecasted and SO are different from what they should be, can you please confirm.

Having your data as you have and making two measures you can achieve the desired result:

```Net Inventory (Forecast) =
SUM ( Table1[Qty in Lot] )
- CALCULATE (
SUM ( Table1[Forecast Qty] );
FILTER ( ALL ( Table1[Week] ); Table1[Week] <= MAX ( Table1[Week] ) );
FILTER (
ALL ( Table1[Part Number] );
Table1[Part Number] = MAX ( Table1[Part Number] )
)
)```
```Net Inventory (SO) =
SUM ( Table1[Qty in Lot] )
- CALCULATE (
SUM ( Table1[Sales Order Qty] );
FILTER ( ALL ( Table1[Week] ); Table1[Week] <= MAX ( Table1[Week] ) );
FILTER (
ALL ( Table1[Part Number] );
Table1[Part Number] = MAX ( Table1[Part Number] )
)
)```

Check the result below and PBIX file attach. Regards,

MFelix

Proud to be a Datanaut!

11 REPLIES 11 Super User

## Re: Net Inventory Over Time

Looking at your table believe that the dates are mixed up so your columns for forecasted and SO are different from what they should be, can you please confirm.

Having your data as you have and making two measures you can achieve the desired result:

```Net Inventory (Forecast) =
SUM ( Table1[Qty in Lot] )
- CALCULATE (
SUM ( Table1[Forecast Qty] );
FILTER ( ALL ( Table1[Week] ); Table1[Week] <= MAX ( Table1[Week] ) );
FILTER (
ALL ( Table1[Part Number] );
Table1[Part Number] = MAX ( Table1[Part Number] )
)
)```
```Net Inventory (SO) =
SUM ( Table1[Qty in Lot] )
- CALCULATE (
SUM ( Table1[Sales Order Qty] );
FILTER ( ALL ( Table1[Week] ); Table1[Week] <= MAX ( Table1[Week] ) );
FILTER (
ALL ( Table1[Part Number] );
Table1[Part Number] = MAX ( Table1[Part Number] )
)
)```

Check the result below and PBIX file attach. Regards,

MFelix

Proud to be a Datanaut!

kschubert417 Frequent Visitor

## Re: Net Inventory Over Time

This works great!

Would you mind telling me how your solution works? I am not very familiar with how those formulas work.

Highlighted Super User

## Re: Net Inventory Over Time

Measures are based on context so depending on the visual and the columns you use or filters the result will be different. Also they allow to referenced previous or next rows.

First of all let me say you can simplify the measure by using this two:

```Net Inventory (Forecast) =
SUM ( Table1[Qty in Lot] )
- CALCULATE (
SUM ( Table1[Forecast Qty] );
FILTER ( ALL ( Table1[Week] ); Table1[Week] <= MAX ( Table1[Week] ) );
ALLEXCEPT (Table1; Table1[Part Number] );
)
Net Inventory (SO) =
SUM ( Table1[Qty in Lot] )
- CALCULATE (
SUM ( Table1[Sales Order Qty] );
FILTER ( ALL ( Table1[Week] ); Table1[Week] <= MAX ( Table1[Week] ) );
ALLEXCEPT (Table1; Table1[Part Number] )    )```

Both measure have the same notation so the explanation is valid for both:

• First part before the minus is the sum of the current Qty Lot, since you want to pick up the current qty no need for additional calculation
• The Calculate allows us to add different filter to a measure:
• The FILTER allows us to make the calculation or the Sales Order Qty or Forecast for all Week that are the same as the current row and below so this will make a cumulative sum
• ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns in this case only keeps the part number filter for each line.

Regards,

MFelix

Proud to be a Datanaut!

kschubert417 Frequent Visitor

## Re: Net Inventory Over Time

If I wanted to add another field like incoming inventory (would be added to inventory and not subtracted) for the week how would I add it in the formula you provided?

Thanks again for your help so far! Super User

## Re: Net Inventory Over Time

You should pick up the laswt part of the measure and add change it to:

```CALCULATE (
SUM ( Table1[Incoming Invenotory] );
FILTER ( ALL ( Table1[Week] ); Table1[Week] <= MAX ( Table1[Week] ) );
ALLEXCEPT (Table1; Table1[Part Number] )
)```

Regards,

MFelix

Proud to be a Datanaut!

kschubert417 Frequent Visitor

## Re: Net Inventory Over Time

Hi @MFelix,

I'm wondering if there is another way to get this accomplished? The solution you gave me works great if I want to put the data in a table, but not so much if I want to plot the data. If you take a look at the attached picture, when I have more than one part selected the net inventory (yellow bars) stays at the same level when the bars should be moving like the red line I put on the same picture. My apologies as I just realized this. Let me know what you think!

Thanks, Super User

## Re: Net Inventory Over Time

I was looking at your image and made some tests, and making the x-axis with the week (not with date hierarchy) and having it made by categorical the number are correct.

Can you please tell me how are you setting up your bar chart?

Regards,

MFelix

Proud to be a Datanaut!

kschubert417 Frequent Visitor

## Re: Net Inventory Over Time

@MFelix see the photo below.  Super User

## Re: Net Inventory Over Time

When I tested in my file what I saw was that continuos axis with date gave the incorrect number, go to X-Axis properties and change it to categorical and see if it works as expected.

Regards,

MFelix

Proud to be a Datanaut!

Announcements #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 379 members 3,388 guests
Recent signins:
• Chaldakov • kalyani_quant • RossP96 • mostvp123 • Yikwun831 • ArnePolfliet • tienthb • stevec1 • olgab • ramibraham • preetjawaria • avnish319 • oktakelly • Ladislav 