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

Re: Net Inventory Over Time

Hi @kschubert417 ,

 

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.

 

invetory.png

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

11 REPLIES 11
Highlighted
Super User
Super User

Re: Net Inventory Over Time

Hi @kschubert417 ,

 

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.

 

invetory.png

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

kschubert417 Frequent Visitor
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.

Super User
Super User

Re: Net Inventory Over Time

Hi @kschubert417 ,

 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kschubert417 Frequent Visitor
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
Super User

Re: Net Inventory Over Time

Hi @kschubert417 ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kschubert417 Frequent Visitor
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.

Capture.JPG

 

Let me know what you think!

 

Thanks,

Super User
Super User

Re: Net Inventory Over Time

Hi @kschubert417 ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kschubert417 Frequent Visitor
Frequent Visitor

Re: Net Inventory Over Time

@MFelix see the photo below. 

Capture.JPG

Super User
Super User

Re: Net Inventory Over Time

Hi @kschubert417 ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Ask Amir Anything

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 2019 Community Highlights

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

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 180 members 1,998 guests
Please welcome our newest community members: