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.
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:
And I am trying to get the below output:
Any and all help would be appreciated,
Thanks!
Solved! Go to Solution.
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.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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,
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUnfortunately 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIf 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis works great!
Would you mind telling me how your solution works? I am not very familiar with how those formulas work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |