This topic contains 13 replies, has 2 voices, and was last updated by 
-
Posts
-
Hi I would like to calculate what our sales are each week, what inventory we had each of the last 52 weeks. In order for me to calculate what we had each of the prior weeks I need to take todays OH – transfers in during that period, add back the transfers out during that period. – Any receivings in that period, and add back the sales since then.
See below, it requires for each week several statements to be worked with just to figure out what we had 4 weeks ago.
Would 52 weeks with 4 statements per week be a lot for excel to work with. And is there a better way to roll back our inventory position for historical review.
UNITS RECEIVED 4WK AGO:=CALCULATE(‘tblInvSkuMaster'[RECEIVED TOTAL UNITS],DATESBETWEEN(dCalendar[DATES],TODAY()-28,TODAY()))
UNITS TRANSFERED 4WK AGO:=CALCULATE(‘tblInvSkuMaster'[TRANSFER TOTAL UNITS],DATESBETWEEN(dCalendar[DATES],TODAY()-28,TODAY()))
UNITS SOLD 4WK AGO:=CALCULATE(‘tblInvSkuMaster'[SALES TOTAL UNITS SOLD],DATESBETWEEN(dCalendar[DATES],TODAY()-28,TODAY()))
4WK AGO ON HAND TOTAL:=CALCULATE(tblInvSkuMaster[ON HAND + IN TRAN TOTAL]-‘tblInvSkuMaster'[UNITS RECEIVED 4WK AGO]+tblInvSkuMaster[UNITS SOLD 4WK AGO])
4 WK AGO SALES:=CALCULATE(SUM(‘SALES TABLE'[UNITS SOLD]),DATESBETWEEN(dCalendar[DATES],TODAY()-28,TODAY()-22))
4 WK AGO S/T OH VS SALES:=IFERROR(CALCULATE([4 WK AGO SALES]/[4WK AGO ON HAND TOTAL]),BLANK())
4 WK AGO NET RETAIL:=CALCULATE(SUM(‘SALES TABLE'[NET RETAIL]),DATESBETWEEN(dCalendar[DATES],TODAY()-28,TODAY()-22))
4 WK AGO GOAL ALL:=CALCULATE(SUM(‘dGOALS'[GOAL ALL]),DATESBETWEEN(dCalendar[DATES],TODAY()-28,TODAY()-22))
4 WK AGO GOAL VS SALES:=([4 WK AGO NET RETAIL]/[4 WK AGO GOAL ALL])-1
This is the cube formula I use with the same basic idea to figure out what we had OH 52 weeks ago. It combines a few formula for result.
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[1YR AGO OH RETAIL]”,”[dCLASS].[CLASS].[All]”,”[tblLocation].[loc_code].&[” & C5 & “]”,Slicer_YEAR1,Slicer_MONTH1,Slicer.MGMT_TRANS_DATE,Slicer.MGMT_CLASS,Slicer.MGMT_BRAND)-CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[1YR AGO RETAIL TRANSFERED]”,”[dCLASS].[CLASS].[All]”,”[TRANSFERS].[loc# to].&[” & C5 & “]”,Slicer_YEAR1,Slicer_MONTH1,Slicer.MGMT_TRANS_DATE,Slicer.MGMT_CLASS,Slicer.MGMT_BRAND)+CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[1YR AGO RETAIL TRANSFERED]”,”[dCLASS].[CLASS].[All]”,”[TRANSFERS].[loc# created].&[” & C5 & “]”,Slicer_YEAR1,Slicer_MONTH1,Slicer.MGMT_TRANS_DATE,Slicer.MGMT_CLASS,Slicer.MGMT_BRAND)
There has to be a simpler way. Could you put up some sample data and we could discuss?
Hi, thanks for your reply.
I’ve found your site to be very helpful.
My xlsb file is 37mb. Not sure what I could do.
Is there another way to share it? Dropbox?
Dropbox is OK. Please make sure that the data is “sanitized” (does not reveal any sensitive company information).
You could also filter the contents down to a few parts/items that have a sufficiently long history of activity.
Best regards,
Tom
Good morning Tom, i’ve attached a link.
Just a warning I’m self taught. I do my best to read up, follow sites such as yours and allow life to continue to teach me.
I’m open to any thoughts on speeding things up as my database connects to our server using OBDC on a SQL. I try to limit the data i query to 3 years wherever possible. But for some processes it can take 20 minutes to poll.
https://www.dropbox.com/s/p1tzt84wuwenghm/HBC%20Sample.xlsx?dl=0
Thanks, Steven
Steven,
I have downloaded file, you can disconnect dropbox link.
Tom
Steven,
Interesting. There is a lot going on in your 15 or so tables.
When I was responsible for inventory information, my life was made (relatively) simple because the SQL database had a single table that recorded all inventory movements including transfers, adjustments, receipts, and sales for all SKUs.
If you are importing your data from SQL Server, or other relational database that supports views, you (or your IT Department) could probably append transfers, adjustments, receipts and sales into an equivalent inventory movement table and possibly eliminate all of your week-by-week calculations with a handful of formulas that could work with pivot filter contexts.
Alberto Ferrari and Marco Russo also have some insights into inventory tables, notably in their “Definitive Guide to DAX” on pages 178 – 188 which you would probably find helpful.
Also, instead of using the TODAY(), I like to use a data freshness date based on something like the calendar’s Last_Refreshed column or the Trans Journal’s posting date. The problem I find with TODAY() is that it introduces a change when the underlying data is static, which leads to some people feeling that the same workbook data is giving them different versions of the truth.
Your data model also has many columns, perhaps not all of them are used. During data refresh, it is not just the import that adds to the total time, but also that the formula engine is optimizing each column and over millions of rows that can add minutes to the overall time.
Also, I may have asked before, but are you using Excel 2016 or Excel 2013 at work?
Tom
Hi Tom, unfortunately I am the IT department. The server SQL DB does have some “views” I think. But they are limited to CUSTOMER, SALES, PRODUCT and a few others nothing along the line of Inventory Movement.
I could try to build a SQL statement that pulls all the information and tables together. Not sure if that would be very slow. I thought I had read smaller chunks are more efficient.
Pulling so many tables together would it not slow down the system?
I would connect to the SERVER and filter SALES, RECEIVINGS, TRANSFERS and so on for maybe the last 3 years. Then use the OH as of today to roll back to prior dates?
Thanks for all your help and insights.
Steven
Hi Tom, another question. I want to limit the sales, transfers, receivings and so on to a start date of >1/31/2015
I can only seem to do it in POWER QUERY:
let
Source = Sql.Database(“SERVER”, “DB0001_160517195223”, [Query=”SELECT tblsale.sale_link,#(lf) tblsale.loc_code AS [LOC#],#(lf) tblsale.DATE AS [TRANS DATE],#(lf) tblsale.TIME,#(lf) tblsale.trans_no AS [TRANS#],#(lf) tblsale.customer_code AS [CUST#],#(lf) tblsalecommission.salesperson,#(lf) tblsaleitem.sku_no AS [SKU#],#(lf) tblsaleitem.description,#(lf) Min(tblsalediscount.disc_code) AS [DISCOUNT CODE],#(lf) tblsaleitem.qty AS [UNITS SOLD],#(lf) tblsaleitem.ext_cost AS COST,#(lf) [unit_ext_price] – [unit_ext_best_price] AS DISCOUNT,#(lf) tblsaleitem.unit_ext_price,#(lf) tblsaleitem.unit_ext_best_price AS [NET RETAIL],#(lf) tblsaleitem.line_no,#(lf) tblsale.trans_type,#(lf) tblsale.void_code,#(lf) tblsale.suspended,#(lf) tblsaleitem.affect_inv,#(lf) tblsaleitem.affect_total,#(lf) tblsale.note AS [SALE NOTE],#(lf) tblsaleitem.note AS [SALE ITEM NOTE]#(lf)FROM tblsalediscount#(lf) RIGHT JOIN (tblsale#(lf) INNER JOIN (tblsaleitem#(lf) INNER JOIN tblsalecommission#(lf) ON ( tblsaleitem.line_no =#(lf) tblsalecommission.line_no )#(lf) AND ( tblsaleitem.sale_link =#(lf)tblsalecommission.sale_link ))#(lf)ON tblsale.sale_link = tblsaleitem.sale_link)#(lf)ON ( tblsalediscount.line_no = tblsaleitem.line_no )#(lf)AND ( tblsalediscount.sale_link = tblsaleitem.sale_link )#(lf)GROUP BY tblsale.sale_link,#(lf) tblsale.loc_code,#(lf) tblsale.DATE,#(lf) tblsale.TIME,#(lf) tblsale.trans_no,#(lf) tblsale.customer_code,#(lf) tblsalecommission.salesperson,#(lf) tblsaleitem.sku_no,#(lf) tblsaleitem.description,#(lf) tblsaleitem.qty,#(lf) tblsaleitem.ext_cost,#(lf) [unit_ext_price] – [unit_ext_best_price],#(lf) tblsaleitem.unit_ext_price,#(lf) tblsaleitem.unit_ext_best_price,#(lf) tblsaleitem.line_no,#(lf) tblsale.trans_type,#(lf) tblsale.void_code,#(lf) tblsale.suspended,#(lf) tblsaleitem.affect_inv,#(lf) tblsaleitem.affect_total,#(lf) tblsale.note,#(lf) tblsaleitem.note; “]),
#”Filtered Rows” = Table.SelectRows(Source, each [TRANS DATE] > #datetime(2015, 1, 31, 0, 0, 0)),
#”Changed Type” = Table.TransformColumnTypes(#”Filtered Rows”,{{“TRANS DATE”, type date}, {“TIME”, type time}, {“UNITS SOLD”, Int64.Type}, {“COST”, Currency.Type}, {“DISCOUNT”, Currency.Type}, {“NET RETAIL”, Currency.Type}}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Changed Type”,{{“unit_ext_price”, Currency.Type}}),
#”Filtered Rows2″ = Table.SelectRows(#”Changed Type1″, each ([void_code] = null) and ([affect_total] = true)),
#”Filtered Rows3″ = Table.SelectRows(#”Filtered Rows2″, each [suspended] = null),
#”Renamed Columns” = Table.RenameColumns(#”Filtered Rows3″,{{“unit_ext_price”, “RETAIL”}, {“description”, “DESCRIPTION”}})
in
#”Renamed Columns”But this is the starting point from my SQL statement. Should I filter the dates here? And if so how? I keep getting error messages when I try. My statement must be wrong.
SELECT tblsale.sale_link,
tblsale.loc_code AS [LOC#],
tblsale.DATE AS [TRANS DATE],
tblsale.TIME,
tblsale.trans_no AS [TRANS#],
tblsale.customer_code AS [CUST#],
tblsalecommission.salesperson,
tblsaleitem.sku_no AS [SKU#],
tblsaleitem.description,
Min(tblsalediscount.disc_code) AS [DISCOUNT CODE],
tblsaleitem.qty AS [UNITS SOLD],
tblsaleitem.ext_cost AS COST,
[unit_ext_price] – [unit_ext_best_price] AS DISCOUNT,
tblsaleitem.unit_ext_price,
tblsaleitem.unit_ext_best_price AS [NET RETAIL],
tblsaleitem.line_no,
tblsale.trans_type,
tblsale.void_code,
tblsale.suspended,
tblsaleitem.affect_inv,
tblsaleitem.affect_total,
tblsale.note AS [SALE NOTE],
tblsaleitem.note AS [SALE ITEM NOTE]
FROM tblsalediscount
RIGHT JOIN (tblsale
INNER JOIN (tblsaleitem
INNER JOIN tblsalecommission
ON ( tblsaleitem.line_no =
tblsalecommission.line_no )
AND ( tblsaleitem.sale_link =
tblsalecommission.sale_link ))
ON tblsale.sale_link = tblsaleitem.sale_link)
ON ( tblsalediscount.line_no = tblsaleitem.line_no )
AND ( tblsalediscount.sale_link = tblsaleitem.sale_link )
GROUP BY tblsale.sale_link,
tblsale.loc_code,
tblsale.DATE,
tblsale.TIME,
tblsale.trans_no,
tblsale.customer_code,
tblsalecommission.salesperson,
tblsaleitem.sku_no,
tblsaleitem.description,
tblsaleitem.qty,
tblsaleitem.ext_cost,
[unit_ext_price] – [unit_ext_best_price],
tblsaleitem.unit_ext_price,
tblsaleitem.unit_ext_best_price,
tblsaleitem.line_no,
tblsale.trans_type,
tblsale.void_code,
tblsale.suspended,
tblsaleitem.affect_inv,
tblsaleitem.affect_total,
tblsale.note,
tblsaleitem.note;You could try this query which attempts to set the date filter using the DateValue() function:
SELECT tblsale.sale_link, tblsale.loc_code AS [LOC#], tblsale.DATE AS [TRANS DATE], tblsale.TIME, tblsale.trans_no AS [TRANS#], tblsale.customer_code AS [CUST#], tblsalecommission.salesperson, tblsaleitem.sku_no AS [SKU#], tblsaleitem.description, Min(tblsalediscount.disc_code) AS [DISCOUNT CODE], tblsaleitem.qty AS [UNITS SOLD], tblsaleitem.ext_cost AS COST, [unit_ext_price] – [unit_ext_best_price] AS DISCOUNT, tblsaleitem.unit_ext_price, tblsaleitem.unit_ext_best_price AS [NET RETAIL], tblsaleitem.line_no, tblsale.trans_type, tblsale.void_code, tblsale.suspended, tblsaleitem.affect_inv, tblsaleitem.affect_total, tblsale.note AS [SALE NOTE], tblsaleitem.note AS [SALE ITEM NOTE] FROM tblsalediscount RIGHT JOIN (tblsale INNER JOIN (tblsaleitem INNER JOIN tblsalecommission ON ( tblsaleitem.line_no = tblsalecommission.line_no ) AND ( tblsaleitem.sale_link = tblsalecommission.sale_link )) ON tblsale.sale_link = tblsaleitem.sale_link AND tblsale.DATE > DateValue ( "1/31/2015" ) ON ( tblsalediscount.line_no = tblsaleitem.line_no ) AND ( tblsalediscount.sale_link = tblsaleitem.sale_link ) GROUP BY tblsale.sale_link, tblsale.loc_code, tblsale.DATE, tblsale.TIME, tblsale.trans_no, tblsale.customer_code, tblsalecommission.salesperson, tblsaleitem.sku_no, tblsaleitem.description, tblsaleitem.qty, tblsaleitem.ext_cost, [unit_ext_price] – [unit_ext_best_price], tblsaleitem.unit_ext_price, tblsaleitem.unit_ext_best_price, tblsaleitem.line_no, tblsale.trans_type, tblsale.void_code, tblsale.suspended, tblsaleitem.affect_inv, tblsaleitem.affect_total, tblsale.note, tblsaleitem.note;`
From the syntax of your SELECT statement, it looks like your SQL database is running MS Access. Since MS Access is not as robust as SQL Server, running queries with multiple tables could slow your system. Probably getting your data in chunks will be better for you.
Hi Tom I will try your suggestions on selecting dates when I’m at the office today.
As as for the Access statement I first write the query in access as I have More experience doing that. Then I take it to an online site and convert the MS access SQL statement to a sql server statement. Is there a better way of me doing it as I’m not familiar with writing SQL statements. I thought it was just converting it and would now bypass any connections are called to the access database
Hi this is the error message i receive with the new statement:
DataSource.Error: Microsoft SQL: ‘DateValue’ is not a recognized built-in function name.
Details:
DataSourceKind=SQL
DataSourcePath=server;DB0001_160517195223
Message='DateValue' is not a recognized built-in function name.
Number=195
Class=15In the SQL Server code, try:
AND tblsale.DATE > '2015-01-31'
The forum ‘Power Pivot’ is closed to new topics and replies.