Slicers (list, dropdown, between) in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data by date for the first five days of January 2024.

Cell	Value
A1	Date
B1	Sales
A2	2024-01-01
B2	100
A3	2024-01-02
B3	150
A4	2024-01-03
B4	200
A5	2024-01-04
B5	130
A6	2024-01-05
B6	170

Formula Trace

CALCULATE(SUM(Sales[Sales]), FILTER(Sales, Sales[Date] >= StartDate && Sales[Date] <= EndDate))

Step 1: Sales[Date] >= StartDate

Step 2: Sales[Date] <= EndDate

Step 3: FILTER(Sales, Sales[Date] >= StartDate && Sales[Date] <= EndDate)

Step 4: SUM(Sales[Sales]) on filtered rows

Step 5: CALCULATE(...)

Cell Reference Map

    A       B
1 | Date  | Sales |
2 | 2024-01-01 | 100 |
3 | 2024-01-02 | 150 |
4 | 2024-01-03 | 200 |
5 | 2024-01-04 | 130 |
6 | 2024-01-05 | 170 |

Slicer controls: StartDate and EndDate (user selects these to filter dates)

The formula uses the Date and Sales columns. The slicer inputs StartDate and EndDate filter the Date column to select rows.

Result

    A       B       C
1 | Date  | Sales | Filtered Sales Sum |
2 | 2024-01-01 | 100 |                   |
3 | 2024-01-02 | 150 |                   |
4 | 2024-01-03 | 200 |                   |
5 | 2024-01-04 | 130 |                   |
6 | 2024-01-05 | 170 |       750         |

(Assuming slicer selected dates from 2024-01-01 to 2024-01-05)

The final result shows the total sales sum filtered by the slicer date range. Here, all dates are included, so sum is 750.

Sheet Trace Quiz - 3 Questions

What does the FILTER function do in the formula?

A Adds all sales values together

B Selects rows where the date is between StartDate and EndDate

C Changes the date format

D Creates a new column with sales data

Key Result

CALCULATE with FILTER applies slicer-selected conditions to sum values within a date range.