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: