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

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

Hi afaque03,

 

I assume you have a Date column called "Date" in your table.

 

First, use the formula below to add a calculate column in your table.

YearMonth = YEAR ( 'Table1'[Date] ) * 12 + MONTH ( 'Table1'[Date] )

Then you should be able to use the formula below to create a measure for On-Hand Quantity. 

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

 

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.