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
DAX0110's avatar
DAX0110
Resolver V
8 years ago

Hi Iamnvt, can you upload the sample file elsewhere?  I can't download it from the current host.

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: