Forum Discussion

formerlyknownas's avatar
formerlyknownas
Helper I
5 years ago
Solved

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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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

 

formerlyknownas's avatar
formerlyknownas
Helper I
5 years ago

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