✅ Part 1: Sales Overview Dashboard

• Focus: Basic KPIs, visuals, slicers, and page design.
• Teaches: Using bar charts, treemaps, line charts, map visual, and cards.

---

📊 Part 2: Advanced Time Intelligence and DAX

Objective: Teach key time-based analysis using DAX measures.

🧠 Questions / Tasks:

1 YTD Sales:

Create a measure to calculate Year-To-Date Sales.

Sales YTD = TOTALYTD(SUM('Order Details'[Sales]), 'Orders'[OrderDate])

2. Previous Year Sales vs. This Year Sales
   Compare total sales of current year vs. previous year using a line or bar chart.

Sales Previous Year = CALCULATE(SUM('Order Details'[Sales]), DATEADD('Orders'[OrderDate], -1, YEAR))

3. Growth % YoY:
   Add a KPI or card visual showing YoY growth in %.

 YoY Growth % = DIVIDE([Sales] - [Sales Previous Year], [Sales Previous Year], 0)

4. Rolling 3-month Sales:
   Create a rolling 3-month sum of sales.

Rolling 3M Sales = CALCULATE(SUM('Order Details'[Sales]), DATESINPERIOD('Orders'[OrderDate], MAX('Orders'[OrderDate]), -3, MONTH))

-- If you have DateTable
Rolling 3M Sales = 
CALCULATE(
    SUM('Order Details'[Sales]),
    DATESINPERIOD('DateTable'[Date], MAX('DateTable'[Date]), -3, MONTH)
) 

Rolling 3-Month Sales means the total sales made in the most recent 3-month period, calculated continuously. For example, if you’re viewing data for June, it sums sales from April, May, and June — always showing the last 3 months of data for each point in time.

---

🧮 Part 3: Customer Segmentation & RFM Analysis

Objective: Analyze customer behavior and value.

🧠 Questions / Tasks:

1. Top 20% Customers Contributing to Sales
   Use RANKX and cumulative % logic to identify top contributors.

Step 1 – Base Sales Measure

Total Sales = SUM('Order Details'[Sales])

Step 2 – Sales by Customer

Sales by Customer = [Total Sales]
    
This ensures we calculate each customer’s total sales without losing other filters.
Step 3 – Rank Customers by Sales

Customer Rank =
RANKX(
    ALL(Customers),
    [Sales by Customer],
    ,
    DESC
)

Step 4 – Cumulative Sales %

Cumulative Sales % =
VAR TotalSalesAll = CALCULATE([Total Sales], ALL(Customers))
VAR CumulativeSales =
    CALCULATE(
        SUMX(VALUES(Customers[CustomerID]), [Sales by Customer]),
        FILTER(
            ALL(Customers),
            [Customer Rank] <= MAX([Customer Rank])
        )
    )
RETURN
DIVIDE(CumulativeSales, TotalSalesAll)

Cumulative Sales % =
VAR TotalSalesAll =
    CALCULATE([Total Sales], ALL(Customers))
VAR CurrentRank =
    [Customer Rank]   -- store measure result in variable
VAR CumulativeSales =
    CALCULATE(
        SUMX(
            VALUES(Customers[CustomerID]),
            [Sales by Customer]
        ),
        FILTER(
            ALL(Customers),
            [Customer Rank] <= CurrentRank
        )
    )
RETURN
DIVIDE(CumulativeSales, TotalSalesAll)



Step 5 – Flag Top 20% Customers

Top 20% Flag =
IF([Cumulative Sales %] <= 0.2, "Top 20%", "Others")

Step 6 – Visualize

    Create a table with:

        Customer Name

        Sales by Customer

        Customer Rank

        Cumulative Sales %

        Top 20% Flag

    Use conditional formatting or filter where Top 20% Flag = "Top 20%".

3. Customer Frequency
   How many times has a customer ordered? Use COUNTROWS over ‘Orders’.
4. Recency (Last Purchase Date)
   Show latest purchase date for each customer using MAX(OrderDate).

Last Purchase Date = 
MAX(Orders[OrderDate])
Recency (Days) =
DATEDIFF(
    MAX(Orders[OrderDate]),
    TODAY(),
    DAY
)

5. Customer Lifetime Sales
   Create a measure for total sales by customer, then segment (High, Medium, Low).
6. Churn Indicator
   Highlight customers who haven’t ordered in last 6 months.

Churn Indicator =
IF(
    TODAY() - [Last Purchase Date] > 180,
    "Churned",
    "Active"
)

---

📦 Part 4: Product and Category Performance

Objective: Deep dive into product, supplier, and category analytics.

🧠 Questions / Tasks:

1. Best-Selling Product by Profit
   Add a column or measure for Profit = Sales – (Quantity × UnitCost), then rank.
2. Product with Highest Quantity Sold per Region
   Use matrix visual with filters.
3. Low Performing Products (Sales < Threshold)
   Use DAX to flag products with poor sales.
4. Category-wise Average Order Value (AOV) AOV = DIVIDE([Sales], DISTINCTCOUNT('Orders'[OrderID]))
5. Stock Alerts: Below Reorder Level
   Use a table visual with conditional formatting from ‘Products’ table.

---

📍 Part 5: Geographic and Delivery Insights

Objective: Analyze shipping performance and geographic distribution.

🧠 Questions / Tasks:

1. Delivery Time = RequiredDate – OrderDate
   Create a calculated column and visualize average delivery time by country.
2. Delayed Orders (Shipped After RequiredDate)
   Create a column to flag late shipments.
3. Country-wise Sales and Shipping Cost
   Use map visual for sales and bar chart for freight cost.
4. Which Shipper Handles Most Orders?
   Use donut/pie chart with count of orders by ShipperName.
5. Shipping Cost % of Sales Shipping % = DIVIDE(SUM('Orders'[Freight]), [Sales])

DAX for Question 4.1

Total_Profit_After_Discount_Test = 
SUMX(
    'Order Details',
    VAR CostPrice = 'Order Details'[UnitPrice]
    VAR SellingPrice = 'Order Details'[UnitPrice] * 1.2
    VAR Discount = 'Order Details'[Discount]
    VAR Quantity = 'Order Details'[Quantity]
    VAR SellingPriceAfterDiscount = SellingPrice * (1 - Discount)
    RETURN
        (SellingPriceAfterDiscount - CostPrice) * Quantity
)

4.2 

Top Product Name = 
VAR TopProd =
    TOPN(
        1,
        FILTER(
            ALL('Order Details'),
            'Order Details'[Region] = MAX('Order Details'[Region])
        ),
        CALCULATE(SUM('Order Details'[Quantity])),
        DESC
    )
RETURN
MAXX(TopProd, 'Order Details'[ProductName])

---