ID 40 - Filter Sales Based on Maximum Sales Amount from Specific Date

Max: 45
Try code in DAX.do
→

Syntax

CALCULATE CALCULATETABLE DATE FILTER IF MAX MAXX OR

Related

43 - Calculate Days in Two Months Based on Start and End Date
◀

SQL

SELECT *
FROM Sales
WHERE [Sales Amount] >= (
    SELECT MAX([Sales Amount])
    FROM Sales
    WHERE [Order Date] = '2007-09-10'
);

DAX

EVALUATE
VAR SpecificDateSales =
    CALCULATETABLE (
        Sales,
        Sales[Order Date] = DATE(2007, 9, 10)
    )
VAR MaxSalesAmount = MAXX(SpecificDateSales, Sales[Sales Amount])
RETURN
    FILTER(
        Sales,
        Sales[Sales Amount] >= MaxSalesAmount
    )

Pandas

import pandas as pd

# Assuming df_sales is the Sales DataFrame
specific_date_sales = df_sales[df_sales['Order Date'] == '2007-09-10']
max_sales_amount = specific_date_sales['Sales Amount'].max()

filtered_sales = df_sales[df_sales['Sales Amount'] >= max_sales_amount]

Output

12345 5000.0 2007-09-11 789 12
54321 7500.0 2007-09-12 456 34

Explanation

In DAX, CALCULATETABLE is used to filter the Sales table for sales made on 2007-09-10. The MAXX function is used to compute the maximum sales amount from this filtered table. The FILTER function then returns all sales where the Sales Amount is greater than or equal to this maximum amount.

Leave a Comment


Previous Next