WITH MaxDate AS (
SELECT MAX(S.OrderDate) AS MaxOrderDate
FROM Sales S
)
SELECT
P.Brand,
SUM(S.SalesAmount) AS TotalSales
FROM
Sales S
JOIN
Product P ON S.ProductID = P.ProductID
JOIN
Date D ON S.OrderDate = D.Date
CROSS JOIN
MaxDate M
WHERE
D.Date BETWEEN DATEADD(MONTH, -6, M.MaxOrderDate) AND M.MaxOrderDate
GROUP BY
P.Brand;
EVALUATE
VAR max_date =
CALCULATE ( MAX ( Sales[Order Date] ) )
VAR newtable =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Sales, 'Product'[Brand] ),
"Total Sales", CALCULATE ( [Sales Amount] )
),
DATESINPERIOD ( 'Date'[Date], max_date, -6, MONTH )
)
RETURN
newtable
import pandas as pd
from datetime import timedelta
# Assuming Sales, Product, and Date are DataFrames
max_date = Sales['OrderDate'].max() # Get the max OrderDate
start_date = max_date - timedelta(days=180) # Calculate start date for last 6 months
# Merge Sales with Product and Date tables
merged_df = pd.merge(Sales, Product, on='ProductID')
merged_df = pd.merge(merged_df, Date, left_on='OrderDate', right_on='Date')
# Filter rows within the last 6 months
filtered_df = merged_df[(merged_df['Date'] >= start_date) & (merged_df['Date'] <= max_date)]
# Group by Product Brand and calculate Total Sales
result_df = filtered_df.groupby('Brand').agg(TotalSales=('SalesAmount', 'sum')).reset_index()
# Convert result to list of lists format
result = result_df.values.tolist()
| Brand A | 34000 |
| Brand B | 50000 |
| Brand C | 27000 |
max_date: Calculates the maximum order date from the Sales table. DATESINPERIOD: Filters the 'Date' table for the last 6 months from the max_date. SUMMARIZE: Groups the sales by 'Product'[Brand]. ADDCOLUMNS: Adds a calculated column for total sales using the [Sales Amount] measure.
Leave a Comment