Calculate On Hand quantity

[afaque03]

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

[v-ljerr-msft]

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]

@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 · Accepted solution]

Hi @afaque03,

My mistake.

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

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]

@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.

[afaque03]

@v-ljerr-msftThnx a lot for your solution. It worked perfectly fine.

[v-ljerr-msft]

Hi @afaque03,

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

Regards

[afaque03]

@v-ljerr-msftSure. Thnx again