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

formerlyknownas's avatar
formerlyknownas
Helper I
5 years ago

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.  

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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.