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

calculating forecasted quantity

I've made a query that pulls supply and demand documents from our ERP in the following format (aside from the column in yellow which is something I made in Excel for the purpose of this question).  I'm trying to find a way to do a calculation to get a Forecasted Available quantity (yellow column) that considers Supply (TransGrp 1) and Demand (TransGrp 2) quantities over a period of time.  Having a Forecasted Available column would allow me to make a very useful line graph, so it's possible to idenity times when we need to produce more or less based on forecasted demand and what we have already scheduled for production.  Any ideas on how to achieve this are greatly appreciated.

 

formerlyknownas_0-1619704354397.png

 

 

 

1 ACCEPTED SOLUTION

Hi, @formerlyknownas 

Thank you for your feedback.

please try the below.

As you can see, I changed SUMX to MAXX. (There are two parts that I used SUMX)

It did not matter on the table visualization when just showing the numbers row by row. Because two rows can represent one date.

However, when showing on the line chart, one data point on x-axis cannot represent two rows that are showing the same date. So, when using sumx, the number format of 29th Apr would be doubled, and that would be larger than the number format of 1st Aug. That was the reason why it showed the last value which is 255. If you change it to maxx, then it will show the last value of 29th Apr. If you change it to Minx, then it will show the first value of 29th Apr.

 

Forcasted Available =
VAR linerank =
RANKX (
ALLSELECTED ( Data ),
CALCULATE ( maxx( Data, Data[DocDate] + Data[DocNum] / 1000000 ) ),
,
ASC
)
VAR currentitemcode =
MAX ( Data[ItemCode] )
RETURN
CALCULATE (
[QtyUnit Total],
FILTER (
ALLSELECTED ( Data ),
RANKX (
ALLSELECTED ( Data ),
CALCULATE ( MaxX( Data, Data[DocDate] + Data[DocNum] / 1000000 ) ),
,
ASC
) <= linerank
&& Data[ItemCode] = currentitemcode
)
)
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

8 REPLIES 8

Hey @Jihwan_Kim 

 

I did a test in Power BI where I have included a new column called ItmGrp along with the measures you suggested (https://www.dropbox.com/s/aik02jtmh54vc4g/forecasted-available-test.pbix?dl=0).

 

I need the Forecasted Quantity number to exclude any lines where the column ItmGrp contains the letter "W".

 

Then I need to be able to plot the Foreccasted Quantity on a Line Chart.  I've attempted to do so as you will see in the pbix file, but I cannot figure out why the Forecasted Available number on the 29/04/2021 is at 255.  It should be 90 since that is the result after subtracting 10 and 1 from the initial supply of 101 as seen on the previous day.

 

formerlyknownas_0-1619720516201.png

 

Hi, @formerlyknownas 

Thank you for your feedback.

please try the below.

As you can see, I changed SUMX to MAXX. (There are two parts that I used SUMX)

It did not matter on the table visualization when just showing the numbers row by row. Because two rows can represent one date.

However, when showing on the line chart, one data point on x-axis cannot represent two rows that are showing the same date. So, when using sumx, the number format of 29th Apr would be doubled, and that would be larger than the number format of 1st Aug. That was the reason why it showed the last value which is 255. If you change it to maxx, then it will show the last value of 29th Apr. If you change it to Minx, then it will show the first value of 29th Apr.

 

Forcasted Available =
VAR linerank =
RANKX (
ALLSELECTED ( Data ),
CALCULATE ( maxx( Data, Data[DocDate] + Data[DocNum] / 1000000 ) ),
,
ASC
)
VAR currentitemcode =
MAX ( Data[ItemCode] )
RETURN
CALCULATE (
[QtyUnit Total],
FILTER (
ALLSELECTED ( Data ),
RANKX (
ALLSELECTED ( Data ),
CALCULATE ( MaxX( Data, Data[DocDate] + Data[DocNum] / 1000000 ) ),
,
ASC
) <= linerank
&& Data[ItemCode] = currentitemcode
)
)
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thanks @Jihwan_Kim 

 

Might you be able to advise on how I can exlcude the lines where ItmGrp has "W" in the solution?

 

And yes, makes sense to use the MAXX function. Thanks for explaining that.  The graph is looking great now!

formerlyknownas_0-1619724234093.png

 

Hi, @formerlyknownas 

Please check the link down below.

Sorry to say that I quite did not understand how to exclude W.

So, I created two versions and you can select one of those that suits your case.

first version: I just amended the same measure to that counts W as zero. Which means that the cumulate total continues until the end of the date in the table but considers W as zero.

second version: totally get rid of the W information and does not shows the date of W as well.

 

https://www.dropbox.com/s/ohi7472egvdtuav/forecasted-available-test.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hey @Jihwan_Kim - v2 is what I'm after.  Thanks so much for the help!

Hey Jihwan, 

 

Thanks so much @Jihwan_Kim . Looks very promising.  I see now that I failed to mention that I need to be able to filter out lines if for example TransGrp is a value other than 1 or 2. I may have another column I want to filter on if it contains a certain text, so I'm curious how I can include those conditions.  

Hi, @formerlyknownas 

Thank you for your feedback.

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thank you.

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

Hi, @formerlyknownas 

 

Please correct me if I wrongly understood your question.

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

 

Picture7.png

 

Forcasted Available =
VAR linerank =
RANKX (
ALLSELECTED ( Data ),
CALCULATE ( SUMX ( Data, Data[DocDate] + Data[DocNum] / 1000000 ) ),
,
ASC
)
VAR currentitemcode =
MAX ( Data[ItemCode] )
RETURN
CALCULATE (
[QtyUnit Total],
FILTER (
ALLSELECTED ( Data ),
RANKX (
ALLSELECTED ( Data ),
CALCULATE ( SUMX ( Data, Data[DocDate] + Data[DocNum] / 1000000 ) ),
,
ASC
) <= linerank
&& Data[ItemCode] = currentitemcode
)
)
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.