Calculate changes between dates for various data categories

Petanek333 — Fri, 19 Aug 2022 15:37:43 GMT

Hi,

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?

Re: Calculate changes between dates for various data categories

Anonymous — Fri, 19 Aug 2022 16:18:35 GMT

Hi Petanek333 - I would like to suggest simplifying the DAX by changing the way you import the data. This would involve removing the "Type" (increase / decrease) and changing the Quantity to +/-. Now the DAX would simply add the quantity movements between the required dates.

Re: Calculate changes between dates for various data categories

Petanek333 — Fri, 19 Aug 2022 17:14:49 GMT

Hi Anonymous and thank you for your suggestion. I will do it. Will be still struggling with the DAX though 🙂

topic Re: Calculate changes between dates for various data categories in DAX Commands and Tips

Calculate changes between dates for various data categories

Re: Calculate changes between dates for various data categories

Re: Calculate changes between dates for various data categories