topic Get values from the previous row - STOCK ON HAND in DAX Commands and Tips

Get values from the previous row - STOCK ON HAND

marie_joy — Thu, 16 May 2024 09:59:56 GMT

Hi,

Sorry I've posted the same question before but maybe it was not well explained...

I'm building a Power BI report to track stock and inventory levels for various items across different locations. My data source is the item ledger entry table from Navision stored in SQL Server. This table captures item movements for each date and location.

I've imported the data using a SQL query: now I have a table where I have stock data for days where I had a movement.

Example:

Posting Date	Item No	Location Code	Quantity
2024-05-10	ITEM-A	LOC-1	10
2024-05-11	ITEM-B	LOC-2	5
2024-05-12	ITEM-A	LOC-1	-2
2024-05-14	ITEM-C	LOC-2	15

Now I would like to have stock values for the previous days. This stock value is equal to the stock value from the previous day:

Expected results (0 are not necessary):

Posting Date	Item No	Location Code	Quantity
2024-05-10	ITEM-A	LOC-1	10
2024-05-11	ITEM-A	LOC-1	10
2024-05-11	ITEM-B	LOC-2	5
2024-05-12	ITEM-A	LOC-1	-2
2024-05-12	ITEM-B	LOC-2	5
2024-05-13	ITEM-A	LOC-1	-2
2024-05-13	ITEM-B	LOC-2	5
2024-05-14	ITEM-A	LOC-1	-2
2024-05-14	ITEM-B	LOC-2	5
2024-05-14	ITEM-C	LOC-2	15

I've tried to do that with a SQL query and a cross join but I could not manage it.

Is there a way to do that with a DAX function?

Thanks!

Re: Get values from the previous row - STOCK ON HAND

mark_endicott — Thu, 16 May 2024 09:28:20 GMT

If you are asking for a count of stock values from previous days, I think the EARLIER function may be able to help you.

But due to the samples of data that you have supplied being difficult to interpret, I cannot give you the exact DAX - you will need to make sure you are using filters on Item and Location code for example.

Have a review of this article and it may help.

https://dax.guide/earlier/

Re: Get values from the previous row - STOCK ON HAND

marie_joy — Thu, 16 May 2024 10:01:08 GMT

Hi,

thank you for your reply.

I don't want to count values, I want the value from the previous day. I've tried to update the question for the tables but for some reasons, the table is not in a good format. Sorry for that.

Re: Get values from the previous row - STOCK ON HAND

mark_endicott — Thu, 16 May 2024 12:37:09 GMT

marie_joy - Ok thanks, that data refit has helped.

You can do this with the data all in one table, but the DAX is not the easiest to get your head around, so I will do my best to explain below the code. Create a measure with the following DAX and change the table names:

VAR _item = SELECTEDVALUE ( 'Table (2)'[Item No] ) VAR _loc = SELECTEDVALUE ( 'Table (2)'[Location Code] ) VAR current_date = SELECTEDVALUE ( 'Table (2)'[Posting Date] ) VAR _table = CALCULATETABLE ( ADDCOLUMNS ( 'Table (2)', "max_date", MAX ( 'Table (2)'[Posting Date] ) ), 'Table (2)'[Item No] = _item, 'Table (2)'[Location Code] = _loc, 'Table (2)'[Posting Date] < current_date ) VAR prev_date = MAXX ( _table, [max_date] ) RETURN CALCULATE ( SUM ( 'Table (2)'[Quantity] ), FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Posting Date] = prev_date && 'Table (2)'[Item No] = _item && 'Table (2)'[Location Code] = _loc ) )

VAR creates Variables for the necessary data to be stored and referenced back to.

SELECTEDVALUE takes the items from the current filter context.

CALCULATETABLE creates a virtual table with the filters I have set after ADDCOLUMNS which is used to find the maximum date that is before the current date.

MAXX allows me to iterate through this virtual table and find that date

CALCULATE allows me to change the current filter context

ALL removes any filter context and the filters afterwards replace it.

As the screenshot below shows, the last column shows the previous days Quantity Value.

If this works for you, please mark this as the solution.

Re: Get values from the previous row - STOCK ON HAND

mark_endicott — Wed, 22 May 2024 10:07:53 GMT

marie_joy - Can you please confirm if this solution has worked for you?