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 report looks like the below image. In this report I have two columns of Incoming and outgoing quantity and now I want to calculate On hand quantity for each month product wise (the data which are blurred by blue pen is product name) and the calculation  for On hand quantity is -:

"suppose if in filter I select 3 months jan, feb and march of 2017 so On hand quantity for jan will be all the incoming quantity till dec 2016 - all outgoing quantity till dec 2016. and for feb will be will be all the incoming quantity till jan 2017 - all outgoing quantity till jan 2017

 

NOTE-: Any month can be selected from that slicer any number of month of any year so it should calculate the total quantity till the previous month, Suppose I select march, april in that slicer so for march it should calculate all the incoming quantity till feb and all outgoing quantity till feb and for april all incoming quantity till march and outgoing quantity till march and then subtract incoming and outgoing quantity.

ReportFilter

 

 

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

7 Replies

  • 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