DAX Approach to Inventory Problem?

Question

I have data in Azure Analysis Services (Tabular) that looks like the following table. I need to create two calculated columns for the Date that the Inventory Changed and the Amount of the Inventory Change. I think and IF function can take care of me for the date of the inventory changed. However, I'm stumped regarding how to calculate the amount of the inventory change.

What are some logical approaches to that?

Answer 1

@james.h.robinson.csm@gmail.com I would use variables to capture the values of the columns you are interested in for the current row and then use MAXX to get the date of last change

Calc Date Inv Change =  
var _sku = 'Table'[SKU]  
var _number = 'Table'[Current Inventory Number]  
var _date = 'Table'[Inventory Snapshot Date]  
return maxx( Filter( 'Table', 'Table'[SJU] = _sku && 'Table'[Inventory Snapshot Date] < _date && 'Table'[Current Inventory Snapshot] <> _number), 'Table'[Inventory Snapshot Date] )  

Then once you have this date you can look up the previous value again. I'm using MAXX again here, but it will be calculating the max over a single row, so it's simply a way of turning a column reference into a scalar value.

Calc Amount of Inv Change =  
var _sku = 'Table'[SKU]  
var _currentNumber = 'Table'[Current Inventory Number]  
var _date = 'Table'[Calc Date Inv Change]  
var _prevNumber = maxx( Filter( 'Table', 'Table'[SJU] = _sku && 'Table'[Inventory Snapshot Date] = _date ), 'Table'[Current Inventory Number] )  
return _currentNumber - _prevNumber