I have a table like this.
and a date slicer 'Calendar'[Dates]
There is data about stock movement in the table StockTable.
Column "Type" is for distinguishing between increase (1) and decrease (2) of stock.
I have several IDs and Warehouses (those are two data categories I want to analyze).
Column "Quantity" represents the amount of stock that has increased or decreased.
Finally column "Current stock" represents the value of stock after the change.
I am struggling with the DAX expressions for the following views: (I suppose more than one DAX formula will be necessary)
With this slicer selection...
...I want to see this (obviously the the column names don't have to include the selected dates)
There are some key points:
- the formula has to find the nearest earlier date in order to get a stock value for that date (for both starting and ending date)
- it has to distinguish between decrease and increase by the "Type" column as there are absolute values (or should I use the calculated column instead?)
...and of course the same for the Warehouse as there are several ID-Warehouse combinations.
Can you help me with the DAX expression please?