Stock Movements Report - Best Approach?

Anything related to PowerPivot and DAX Formuale
Jonny1010101010
Posts: 11
Joined: Wed Oct 17, 2018 5:23 am

Stock Movements Report - Best Approach?

Postby Jonny1010101010 » Sun Dec 02, 2018 10:05 pm

Hi all,

I've working on a report at the moment that shows the pallets which have come into, or left, stock over a 24 hour period. I can only infer these movements from comparing stock reports generated at 5am each morning. I cannot get a transaction report from the warehouse.

I have done this exercise previously using the following method - enter the two days stock into a table, create a pivot with one day next to the other and using a combination of (pallet ID / Sell By Date / Product Code) as the key with which to match the days. I can then do some calculations off the back of this. This works fine, but this approaches means running a report everyday.

I want to automate this further & be able to trend my results easily over time. To do this, I wish to load several dates worth of stock reports, and then within my Data Model, arrive at a set of metrics based on the day-to-day movements described above.

My question - I've started the process of both using Power Query & Power Pivot, but I'm not sure what the cleanest approach would be? Also, do you have any tips regarding how I should handle each days stock within the model - effectively, every day will be both the first and second day of the comparison, and I'm unsure if this will introduce complexities.

Thanks, Jonny

Jonny1010101010
Posts: 11
Joined: Wed Oct 17, 2018 5:23 am

Re: Stock Movements Report - Best Approach?

Postby Jonny1010101010 » Mon Dec 03, 2018 5:35 am

Hi again,

I've attached a workbook showing the raw information ('Before') and the comparison table I already make each day. I'm trying to automate the creation of 'After', for instance, over 5 days, and calculate five sets of results between each of those days.

Thanks, Jonny

Stock Comparison - Day On Day.xlsx
(13.06 KiB) Downloaded 3 times

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Stock Movements Report - Best Approach?

Postby MattAllington » Mon Dec 03, 2018 5:40 am

You can use semi additive measures for reporting on SOH

https://www.sqlbi.com/articles/semi-add ... es-in-dax/

Generally I would try to create a perpetual inventory by breaking the problem into pieces per day

Opening stock +/- movements = SOH. You need a specific FILTER function to calculate the numbers, but the exact formula depends on your data. Can you post a sample workbook with indicative data? [edit: I started my reply before you posted the sample, hence it wasn’t there when I answered.]. I will take a look when I can.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Stock Movements Report - Best Approach?

Postby MattAllington » Mon Dec 03, 2018 7:31 am

Take a look at this as a possible solution
Stock Comparison - Day On Day.xlsx
(331.93 KiB) Downloaded 4 times
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Jonny1010101010
Posts: 11
Joined: Wed Oct 17, 2018 5:23 am

Re: Stock Movements Report - Best Approach?

Postby Jonny1010101010 » Tue Dec 04, 2018 3:30 am

Wow, glad you've pointed me towards this - I wouldn't have arrived at this through Googling thats for sure!

II'll have to digest this - but one question in the meantime - does the filter context play at active role in these measures?

Ideally, the model would analyse 5 days worth of stock movements based on stock holding reports at each day end, irrespective of how that comparison is manifested in the Excel file, eg I don't need to have a comparison pivot table at all but the calculation is happening in the data model. Theoretically the file size of this kind of comparison pivot would get out of hand pretty fast, and I intend to use this process over many month's worth of data.

An example output from this summary pivot could be - "For Product 1 over the last five days, there has been 150 units despatched from stock".

Any thoughts? And thanks again :)

Jonny

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Stock Movements Report - Best Approach?

Postby MattAllington » Tue Dec 04, 2018 5:14 am

The solution I wrote is hard coded to compare any date with the previous date. Are you saying you want to compare any date with any other date and see the change from the 2 dates? If so, I would suggest adding a second Calendar containing a single FromDate colum. Don’t join it to any other tables. Add it as a slicer so you can select the starting date. Write a measure SelectedFrom = Max(calendar2[fromDate]). Replace the max(date) in my original formula with this new measure. I think that will work. What’s more, you should be able to flip the slicer paradigm. Place a slicer on the original calendar date column and place the fromdate in the pivot. You should be able to see the history in a table leading up to the selected todate. Let me know if you need help doing that

Edit: actually, now I think abou it, I guess you want to see the aggregate daily change, and that would be harder. It would be helpful if you can update the version I posted with more data so it can be used in the solution and explain what you would like to see
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Jonny1010101010
Posts: 11
Joined: Wed Oct 17, 2018 5:23 am

Re: Stock Movements Report - Best Approach?

Postby Jonny1010101010 » Tue Dec 04, 2018 7:06 am

Right, more food for thought! :)

It's probably best, as you say, to prepare more data to work with. I'll see how far I get understand what you've already outlined.

Thanks

Jonny1010101010
Posts: 11
Joined: Wed Oct 17, 2018 5:23 am

Re: Stock Movements Report - Best Approach?

Postby Jonny1010101010 » Wed Dec 05, 2018 3:15 am

Hi,

Yes Matt, the edit you made describes what I'm looking to do. I've attached some mock data over about 30 days (edit: alot less actually - I couldn't upload the original file!) that I'm going to play with.

stock_table2.xlsx
(721.85 KiB) Downloaded 2 times


Thanks, Jonny

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Stock Movements Report - Best Approach?

Postby MattAllington » Wed Dec 05, 2018 5:35 pm

I don't see any stock movement in the table, just SOH. So therefore I guess you just want to compare any two dates SOH. I think this version does this. Hopefully it is self explanatory.
Stock Comparison - Day On Day (1).xlsx
(1.36 MiB) Downloaded 2 times
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 5 guests

cron