Forum Discussion

afaque03's avatar
afaque03
Helper I
9 years ago
Solved

Calculate On Hand quantity

Hello Freinds,   I have a matrix table where im showing month wise incoming and outgoing quantity and there is a filter of month. I want to calculate On-Hand quantity for the selected month. My rep...
Need Help
Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi afaque03,

     

    My mistake.

     

    Could you try the formula below to see if it works? :smileyhappy:

    On-Hand Quantity =
VAR currentYearMonth =
    MAX ( 'Table1'[YearMonth] )
VAR productName =
    FIRSTNONBLANK ( 'Table1'[Product Name], 1 )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Incoming Quantity] ) - SUM ( 'Table1'[Outgoing Quantity] ),
        FILTER (
            ALL ( Table1 ),
            'Table1'[YearMonth]
                <= currentYearMonth - 1
                && 'Table1'[Product Name] = productName
        )
    )

     

    Regards

afaque03's avatar
afaque03
Helper I
9 years ago

v-ljerr-msftThnx for the solution. But it does not give me quantity product wise I want it to be product wise On-Hand quantity. That measure is giving me a single output for each product. for example for the month of january it is giving me 435615.86 for each product.

 

 

 

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi afaque03,

 

My mistake.

 

Could you try the formula below to see if it works? :smileyhappy:

On-Hand Quantity =
VAR currentYearMonth =
    MAX ( 'Table1'[YearMonth] )
VAR productName =
    FIRSTNONBLANK ( 'Table1'[Product Name], 1 )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Incoming Quantity] ) - SUM ( 'Table1'[Outgoing Quantity] ),
        FILTER (
            ALL ( Table1 ),
            'Table1'[YearMonth]
                <= currentYearMonth - 1
                && 'Table1'[Product Name] = productName
        )
    )

 

Regards

  • afaque03's avatar
    afaque03
    Helper I
    9 years ago

    v-ljerr-msftThnx a lot for your solution. It worked perfectly fine. :smileyvery-happy:

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi afaque03,

       

      Great to hear the problem got resolved! Could you accept the corresponding reply as solution to close this thread? :smileyhappy:

       

      Regards

  • afaque03's avatar
    afaque03
    Helper I
    9 years ago

    v-ljerr-msftSorry to bother you again. Can you help me to make the change in the DAX in case when there is no incoming and outgoing then On-Hand quantity of that month should get carry forwarded to onhand quantity of next month. Currently the DAX does not carry forward the On-Hand quantity. 

    "On-Hand Quantity = VAR currentYearMonth = MAX (Inventory[YearMonth] ) VAR productName = FIRSTNONBLANK ( Inventory[Product Name], 1 ) RETURN CALCULATE ( SUM ( Inventory[Incoming Quantity] ) - SUM ( Inventory[Outgoing Quantity] ), FILTER ( ALL ( Inventory ), Inventory[YearMonth] <= currentYearMonth - 1 && Inventory[Product Name] = productName ) ) ". Attached is the snapshot of that report in which for the month of may the On-Hand quantity is 684 and when there is no incoming and outgoing quantity for june the On-Hand quantity also becomes blank.