[sjhc1177Participant Started: 25Replies: 28Total: 53]

Sorry to ask another question. I’m also looking to get the same formula for 1 year ago. Here is my old formula:

 

1YR AGO TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0,dateadd(dCalendar[DATES],-364,DAY))-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0,dateadd(dCalendar[DATES],-364,DAY))

 

Steven

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Wow, thanks a lot. That totally did the trick.

 

I’ve never used “ADDCOLUMNS”, I must read up on it.

I thought “SUMX” was to be avoided? Something about being slow over a large system?

 

Steven

Cheers

[sjhc1177Participant Started: 25Replies: 28Total: 53]

See attached image that didn’t display in the post.

Attachments:

You must be logged in to view attached files.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

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=15

[sjhc1177Participant Started: 25Replies: 28Total: 53]

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

[sjhc1177Participant Started: 25Replies: 28Total: 53]

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;

[sjhc1177Participant Started: 25Replies: 28Total: 53]

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

[sjhc1177Participant Started: 25Replies: 28Total: 53]

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

[sjhc1177Participant Started: 25Replies: 28Total: 53]

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?

[sjhc1177Participant Started: 25Replies: 28Total: 53]

<span style=”color: #333333;”>For this case yes.

Items are received against the receiving table to the warehouse. And then transferred from the warehouse to each store.

So for analysis I only need to look at the transfer table and the column first sent to stores.

If he item has been sent many times I only look at MAX being the highest number under firsts sent to stores.</span>

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, it only contains merchandise.

 

I have a receiving table that has the item details (model, brand…) And I would like to know just the new items that have been sent to stores so I can track how well or poor they are doing.

 

This would allow us to make more informed discussion on reorders or returns.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi the name of the column that contains the number of weeks on the floor is :”FIRST SENT TO STORES”

 

For now it’s not as important to know which stores it was received for. It more important to know what new products have been sent to the stores and how they have done with it.

 

Thanks, Steven

 

 

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, thanks for reading my post.

• SALES TOTAL NET RETAIL is a measure. SALES TOTAL NET RETAIL:=sum(‘SALES TABLE'[NET RETAIL])
• SALES TOTAL UNITS SOLD:=sum(‘SALES TABLE'[UNITS SOLD])
• TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0)-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0)
• In your formula for [MUGS Trans# Distinct Count], I’m trying to make sure if in the power pivot table the TRANS# is listed more than once. It is counted only once.

What i want to achieve is that if only 1 MUG is sold in a single transaction, to extract the value of the sale. We pay commission to our staff. But when they sell only 1 MUG in a transaction they get 1/2 the normal commission. The commission is in another table. So having it indexed is for a later question. LOL.

I really appreciate all the help people like you provide. It has helped me a lot in my carer and I also try to give back.

 

Cheers

[Author]

Posts