Forum Discussion

Iamnvt's avatar
Iamnvt
Continued Contributor
8 years ago
Solved

Sales - stock Calculation

hi,   I have a Sales table: Order      Date               Product  Sales 1 11/25/2017 A 10 2 11/26/2017 B 20 3 11/27/2017 A 5   and a Stock table: Product    Quantity ...
Need Help
Iamnvt's avatar
Iamnvt
Continued Contributor
8 years ago

it is Onedrive link. My friend can download it well. Could you please try again? DAX0110

DAX0110's avatar
DAX0110
Resolver V
8 years ago

Hi Iamnvt, I did some work on your sample workbook - please see:

 

Modified workbook

 

  • Iamnvt's avatar
    Iamnvt
    Continued Contributor
    8 years ago

    hi,

     

    thanks for the answer. However, it doesn't give the correct result yet.

    Order        Date             Product    Requirement

    111/25/2017A9
    211/26/2017B18
    311/27/2017A5

     

    I understand your approach of running total, and able to modify the DAX to give the correct result.

     

    Questions regarding to the DAX:

    1. What is the purpose of calling the variants with firstnonblank?

    2. when I remove the date field out of the pivot. it seems not working. How can I fix that?

     

    ProductPBI           Total Sold Qty       PBI Running Sold Qty      PBI Stock Qty     PBI Material Requirement

    A1501 
    B2002 

     

     

     

     

    • DAX0110's avatar
      DAX0110
      Resolver V
      8 years ago

      Hi Iamnvt,

       

      Good to see that you can debug my DAX formula - it's always harder to troubleshoot someone else's work than starting over from scratch yourself, so good work!

       

      As for FIRSTNONBLANK - you can also use SELECTEDVALUE if doing this in Power BI Desktop.  It has to be used because there is only a filter context at that point. Try remove it and see what happens.

       

      The pivot table after removing date?  Well it's still working.  This is my screenshot:

       

      • Iamnvt's avatar
        Iamnvt
        Continued Contributor
        8 years ago

         

        I think I found the correct debugged version now. It works even if I removed the date field out of the pivot.

         

        =
        VAR runningQty = [PBI Running Sold Qty]
        VAR thisQty = [PBI Total Sold Qty]
        VAR stockQty = [PBI Stock Qty]
        VAR reqmt = IF( stockQty > runningQty
         , IF(thisQty + runningQty > stockQty
         , thisQty+runningQty - stockQty
         ,BLANK())
         ,thisQty
         )
        RETURN IF( ISBLANK( thisQty ), BLANK(), reqmt )

         

         Still few questions:

        1. About the Running total formula, why do you use the VAR version to calculate that instead of the common cumulative pattern?  

        =CALCULATE(Sales[PBI Total Sold Qty], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))

         

         I tried to replace  [PBI  Running Sold Qty] with the formula above. It gives the correct result, but when moving the Date field out, it is not correct anymore.

         

        What is the correct Running total formula with FILTER if I don't use the VAR version?

         

        2. Is there any other elegant solution? I think it is quite a simple problem; however, the solution seems not easy.