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
Hi,

It is not working when you put the date field into the pivot.

Br
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