Hi,
I would like my POWER QUERY to filter only a select date range.
Sales from 730 days ago to 830 days ago.
Sales from 365 days ago to 465 days ago.
Sales from today to 100 days ago.
Here is my current code:
SELECT tblsale.sale_link,
tblsale.loc_code AS [LOC#],
tblsale.DATE AS [TRANS DATE],
tblsale.TIME,
tblsale.cashier,
tblsale.trans_no AS [TRANS#],
tblsale.customer_code AS [CUST#],
tblsalecommission.salesperson,
tblsaleitem.sku_no AS [SKU#],
tblsaleitem.return_code,
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 >DATEADD(day, -750, convert(date, GETDATE())))
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.cashier,
tblsale.customer_code,
tblsalecommission.salesperson,
tblsaleitem.sku_no,
tblsaleitem.return_code,
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;
I would like my POWER QUERY to filter only a select date range.
Sales from 730 days ago to 830 days ago.
Sales from 365 days ago to 465 days ago.
Sales from today to 100 days ago.
Here is my current code:
SELECT tblsale.sale_link,
tblsale.loc_code AS [LOC#],
tblsale.DATE AS [TRANS DATE],
tblsale.TIME,
tblsale.cashier,
tblsale.trans_no AS [TRANS#],
tblsale.customer_code AS [CUST#],
tblsalecommission.salesperson,
tblsaleitem.sku_no AS [SKU#],
tblsaleitem.return_code,
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 >DATEADD(day, -750, convert(date, GETDATE())))
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.cashier,
tblsale.customer_code,
tblsalecommission.salesperson,
tblsaleitem.sku_no,
tblsaleitem.return_code,
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;