Ending balance of part code

[Drobinson1]

I have a table that takes a snap shot of inventory everynight. Small Example

WAREHOUSE	PART_CODE	QOH_DATE	IC_COST
01	OH00066T	8/14/2017	109945
01	OH00066T	8/13/2017	149000
01	OH0065	1/31/2017	25000
01	OH0065	12/31/2017	2500

I want to use a relative filter using QOH Date

So using relative date of qoh filter I would select range to be 1/1/207 thru 12/31/2017.

Base on that I would want to calculate total starting inventory value on 1/1/2017 and total ending value on 1//31/2018.

My measure as a total works great, but when I try to show the part details it does not work great.

Example:

With the qoh date filter for the whole calender yeat part OH00066T      will still show a start balance of  149000 and end balance of  109945.  Taking the firstdate and last date values and subtracting. 

I would really want it to show 0 and 0 which is the balance of that part code on 1/1/2017 and the balance of that part code on 12/31/2017.

For end inventory I tried 

End Inventory = CALCULATE(SUM(RC_Daily_QOH[IC_COST]),LASTDATE(RC_Daily_QOH[QOH_DATE]))

Again that gives me the last date for that product and not the last date based on the qohdate slicer i have above.  I want to avoid using a seperate disconnected date table.

[v-xjiin-msft]

@Drobinson1

In your scenario, you want to use single column QOH_Date as the field for a slicer to filter a date range. Right?

For this part, in my opinion using a calendar table for slicer will be a better choice.

Also I'm not quite understand about why part OH00066T should show 0 and 0. Let's say the date range is 01/01/2017 to 12/31/2017. Based on your End Inventory expression, the end inventory should be the sum of IC_COST where QOH_DATE is 12/31/2017. Right?

Then what's the relation between this End Inventory to other PART_CODE? Did you mean if IC_COST is bigger than End Inventory, it should be 0? Or anything else.

Please kindly share us more detailed description about your requirement. Also, it will help us more if you can share us your desired result based on the sample data. It'll lead us the right direction.

Thanks,
Xi Jin.

[Drobinson1]

In the attached photo the begin vs end calc works great for everything except the table in the bootm left marked off by red pen.

Just to clarify this is a nghtly snap shot of inventory balances.  So we had inventory for OH00066T on 8/13/2017 and then must have moved the inventory out on 8/13/2017 as there is no record after that.

So since there is no record of inventory record on 12/31/2017 for item OH00066T I would want to show 0.

The calculation works great until I introduce a part code's in into a table visual.  The the calculation only takes the lastdate for that product.  In this case gives me the balance of the item on 8/13/2017 instead of the balance on 12/31/2017

01

OH00066T

8/13/2017

149000

[v-xjiin-msft]

@Drobinson1

First in your scenario, your relative date slicer is based on current date. It has nothing to do with the column QOH_DATE.  You can simply verify this by create a measure with DAX expression: SELECTEDVALUE('Table'[QOH_DATE]).

Then based on your End Inventory expression: CALCULATE(SUM(RC_Daily_QOH[IC_COST]),LASTDATE(RC_Daily_QOH[QOH_DATE])). The LastDate() function will always return the current QOH_DATE for each row. It also means that for example, the QOH_DATE for part code OH00066T is 8/13/2017, so the LASTDATE(RC_Daily_QOH[QOH_DATE]) for OH00066T  is always 8/13/2017 not 12/31/2017. That's why the end Inventory for OH00066T is 149000.

So based on the sample data in your original post. The last date is 12/31/2017, it seems like you want to get the max date. If the max date is what you want. You can refer to following method to calculate the End Inventory:

Create a measure to calculate the Max QOH_DATE:

Max Date = CALCULATE(MAX('Table'[QOH_DATE]),ALLSELECTED('Table'))

Then the End Inventory expression can be this:

New End Inventory =
IF (
    MAX ( 'Table'[QOH_DATE] ) = [Max Date],
    CALCULATE ( SUM ( 'Table'[IC_COST] ) ),
    0
)

Thanks,

Xi Jin.

[Drobinson1]

That did not work.  

It gave me a much larger balance than expected.

[Zubair_Muhammad]

@Drobinson1

Try this

End_Inventory =
VAR MaxDate =
    CALCULATE ( MAX ( RC_Daily_QOH[QOH_DATE] ), ALLSELECTED ( RC_Daily_QOH ) )
RETURN
    CALCULATE (
        SUM ( RC_Daily_QOH[IC_COST] ),
        FILTER (
            ALLSELECTED ( RC_Daily_QOH[QOH_DATE] ),
            RC_Daily_QOH[QOH_DATE] = MaxDate
        )
    )