Financial dataset, along with 5 advanced DAX questions & solutions

---

1. Slidescope Sample Financial Dataset (100 Rows)

We’ll use a dataset representing sales transactions. This dataset includes information about Date, Product Category, Product Name, Sales Amount, Quantity, Customer Segment, and Region.

Table Name: Sales

Date	Product Category	Product Name	Sales Amount	Quantity	Customer Segment	Region	Order ID
2024-01-05	Electronics	Laptop X	1200	1	Consumer	North	1001
2024-01-10	Clothing	T-Shirt A	25	2	Corporate	South	1002
2024-01-15	Home Goods	Coffee Maker	75	1	Consumer	East	1003
2024-01-20	Electronics	Smartphone Y	800	1	Small Business	West	1004
2024-01-25	Clothing	Jeans B	60	1	Consumer	North	1005
2024-02-01	Home Goods	Blender	100	1	Corporate	South	1006
2024-02-05	Electronics	Tablet Z	500	1	Consumer	East	1007
2024-02-10	Clothing	Dress C	90	1	Small Business	West	1008
2024-02-15	Home Goods	Toaster	40	1	Consumer	North	1009
2024-02-20	Electronics	Smartwatch	300	1	Corporate	South	1010
…	…	…	…	…	…	…	…
2025-05-15	Electronics	Drone P	950	1	Consumer	North	1191
2025-05-20	Clothing	Jacket Q	120	1	Corporate	South	1192
2025-05-25	Home Goods	Air Fryer	110	1	Consumer	East	1193
2025-05-30	Electronics	Gaming Console	450	1	Small Business	West	1194
2025-06-01	Clothing	Skirt R	45	1	Consumer	North	1195
2025-06-05	Home Goods	Robot Vacuum	350	1	Corporate	South	1196
2025-06-10	Electronics	VR Headset	600	1	Consumer	East	1197
2025-06-15	Clothing	Scarf S	30	2	Small Business	West	1198
2025-06-20	Home Goods	Instant Pot	90	1	Consumer	North	1199
2025-06-25	Electronics	Projector T	700	1	Corporate	South	1200

---

Get the Dataset Here: https://docs.google.com/spreadsheets/d/1UiMjf0pELxH6_InvTTpZqdcdwo_trqmZG62Zq9R4VNU/edit?usp=sharing

2. Advanced DAX Questions and Solutions

Here are 5 advanced DAX questions that challenge students to apply concepts like context transitions, time intelligence, iterators, and filter modification.

Make sure you have this measure Measure: Total Sales

First, create a base measure that will be used in subsequent calculations:

Total Sales = SUM(Sales[Sales Amount])

---

Question 1: Sales YTD (Year-to-Date) for Previous Year

Purpose: This question tests understanding of time intelligence functions, specifically SAMEPERIODLASTYEAR and TOTALYTD, and how to combine them to get a comparative value. It also emphasizes the importance of a proper date table.

Scenario: A manager wants to see the Year-to-Date sales for the previous year, relative to the currently selected date context.

DAX Solution:

Sales YTD Last Year =
CALCULATE (
    TOTALYTD ( [Total Sales], 'Date'[Date] ),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Explanation:

1. TOTALYTD([Total Sales], 'Date'[Date]): This calculates the Year-to-Date sum of Total Sales based on the Date column from the Date table. It’s crucial to have a dedicated Date table marked as a date table in Power BI/tabular model for time intelligence functions to work correctly.
2. SAMEPERIODLASTYEAR('Date'[Date]): This is a filter modifier that shifts the current date context back by exactly one year, maintaining the same day, month, and quarter.
3. CALCULATE(...): This function changes the filter context under which the inner expression (TOTALYTD) is evaluated. In this case, it applies the SAMEPERIODLASTYEAR filter before TOTALYTD calculates.

---

Question 2: Sales Growth % (Current vs. Previous Month)

Purpose: This question combines time intelligence (PREVIOUSMONTH) with basic arithmetic to calculate growth. It also introduces the concept of handling division by zero for robust measures.

Scenario: Analyze the month-over-month sales growth percentage to understand recent performance trends.

DAX Solution:

Sales Growth % (MoM) =
VAR CurrentMonthSales = [Total Sales]
VAR PreviousMonthSales =
    CALCULATE ( [Total Sales], PREVIOUSMONTH ( 'Date'[Date] ) )
RETURN
    DIVIDE (
        CurrentMonthSales - PreviousMonthSales,
        PreviousMonthSales,
        0 // Handle division by zero
    )

Explanation:

1. VAR CurrentMonthSales = [Total Sales]: This variable stores the Total Sales for the current filter context (i.e., the current month selected).
2. VAR PreviousMonthSales = CALCULATE ( [Total Sales], PREVIOUSMONTH ( 'Date'[Date] ) ): This variable calculates Total Sales for the previous month. PREVIOUSMONTH('Date'[Date]) is a filter modifier that returns a table that is one month behind the current context.
3. DIVIDE(Numerator, Denominator, [AlternativeResult]): This safe division function is used to prevent errors when PreviousMonthSales is zero or blank. The formula for growth is (Current - Previous) / Previous.
4. The RETURN statement calculates the growth percentage using the defined variables.

---

Question 3: Top N Products by Sales (Dynamic N)

Purpose: This question introduces TOPN for ranking and CALCULATE for context modification. It also demonstrates how to use ALLSELECTED to respect existing filters while removing filters from a specific column for ranking.

Scenario: Identify the top N products by sales, where ‘N’ can be selected by the user (e.g., in a slicer). The ranking should consider other filters applied (e.g., specific regions or customer segments).

DAX Solution:

(Assumes you have a disconnected parameter table or a simple measure for N)

Learn How to create a disconnected Top N Table here : https://slidescope.com/what-is-a-disconnected-parameter-table-and-how-to-create-in-power-bi/

// Assuming 'N' is a measure or a value from a disconnected table (e.g., 'TopNValue'[TopN])
// For simplicity, let's assume 'N' is a fixed value for now for demonstration.
// In a real scenario, you'd use a what-if parameter or a disconnected table for user input.
// Let's set N = 5 for this example.

Top 5 Products by Sales =
CALCULATE (
    [Total Sales],
    TOPN (
        5, // Replace '5' with your dynamic 'N' value, e.g., 'TopNValue'[TopN]
        ALL ( Sales[Product Name] ), // Remove filters on Product Name
        [Total Sales], DESC
    )
)

Explanation:

1. TOPN(5, ALL(Sales[Product Name]), [Total Sales], DESC):
   • 5: Specifies that we want the top 5 products. In a real scenario, this would be a reference to a dynamic ‘N’ value from a parameter table.
   • ALL(Sales[Product Name]): This removes any existing filters on the Product Name column. This is crucial because TOPN needs to evaluate all products to find the top ones. If ALLSELECTED were used instead, it would respect filters on other columns (like Region) but remove filters on Product Name.
   • [Total Sales]: The expression by which to rank the products.
   • DESC: Order by Total Sales in descending order.
2. CALCULATE(...): This function changes the filter context. The TOPN function returns a table containing the top N products. CALCULATE then evaluates [Total Sales] only for the rows present in this top N table. This is a classic example of context transition where row context created by TOPN is converted into filter context by CALCULATE.

---

Question 4: Sales Contribution to Parent Category (Percent of Parent)

Purpose: This question tests understanding of ALLEXCEPT or REMOVEFILTERS combined with CALCULATE to calculate a value at a higher hierarchical level and then determine the percentage contribution.

Scenario: Understand how much each product contributes to its respective product category’s total sales.

DAX Solution:

Sales % of Parent Category =
VAR CurrentSales = [Total Sales]
VAR CategorySales =
    CALCULATE (
        [Total Sales],
        ALLEXCEPT ( Sales, Sales[Product Category] ) // Remove all filters except Product Category
    )
RETURN
    DIVIDE ( CurrentSales, CategorySales, 0 )

Explanation:

1. VAR CurrentSales = [Total Sales]: Stores the sales for the current product (or whatever the deepest level in the visual is).
2. VAR CategorySales = CALCULATE ( [Total Sales], ALLEXCEPT ( Sales, Sales[Product Category] ) ):
   • ALLEXCEPT(Sales, Sales[Product Category]): This is a filter modifier that removes all filters from the Sales table except for the filters applied to the Product Category column. This effectively calculates the Total Sales for the entire product category of the current product, ignoring any specific product name filter that might be in place.
   • CALCULATE(...): This evaluates [Total Sales] under the modified filter context, giving us the total sales for the parent category.
3. DIVIDE(CurrentSales, CategorySales, 0): Calculates the percentage contribution.

---

Question 5: Cumulative Sales Over Time (Running Total)

Purpose: This question demonstrates how to create a running total using FILTER and ALL (or ALLSELECTED depending on the desired behavior) to dynamically filter a date range.

Scenario: Visualize the cumulative sales progression over time to understand overall sales trajectory.

DAX Solution:

Cumulative Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( 'Date'[Date] ), // Remove all filters from the Date column
        'Date'[Date] <= MAX ( 'Date'[Date] ) // Keep only dates up to the current date in context
    )
)

Explanation:

1. CALCULATE([Total Sales], ...): The core of most advanced DAX calculations, changing the filter context for [Total Sales].
2. FILTER(ALL('Date'[Date]), ...):
   • ALL('Date'[Date]): This is crucial. It removes any existing filter on the Date column, allowing the FILTER function to iterate over all dates in the Date table, regardless of the current date selected in the visual (e.g., if you’re on a specific month, ALL ensures you see all months).
   • 'Date'[Date] <= MAX('Date'[Date]): This is the condition that creates the running total. For each row in the visual (e.g., each day or month), MAX('Date'[Date]) returns the maximum date in that current context. The FILTER then keeps all dates from the ALL('Date'[Date]) table that are less than or equal to this MAX date, effectively creating a cumulative range.