Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
MFelix
Super User
Super User

Hi @Anonymous ,

 

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

11 REPLIES 11
MFelix
Super User
Super User

Hi @Anonymous ,

 

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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,

Hi @Anonymous ,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix see the photo below. 

Capture.JPG

Hi @Anonymous ,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Unfortunately this does not seem to work for me either. Is there any way to accomplish this by creating a calculated column?

Hi @Anonymous ,

 

Can you share a sample of your file? If sensitive information you can share through private message.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @Anonymous ,

 

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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!

Hi @Anonymous ,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

This works great! 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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