Exercise ID: s1195 Title: KPI With Current Month, 3 Month Average, and Comparison to Last

Phase 1: The 100.1 Title Worksheet

• Create the Field: Create a new field Max Order Date using the DATENAME() function (Section 9.9.3) to display the month, day, and year as a string.

• Build the View: Add Max Order Date to the Rows shelf.

• Hide the Marks: On the Marks card, select the Color tile and set the opacity to 0% (Section 7.3.11) to hide the mark while keeping the data available.

• Dynamic Title: Double-click the worksheet title, delete the default text, and use the Insert menu (Section 11.13.2) to add the Max Order Date field.

• Clean Up: Right-click the field on the Rows shelf and uncheck Show Header (Section 12.9).

Phase 2: The 100.2 % Diff (Comparison) Worksheet

• Calculated Field: Create the % diff from last field to calculate the change compared to the previous month.

• Table Logic: Use the LOOKUP() function (Section 11.10.4) for the Compare to Last field to determine if performance is "Up" or "Down".

• Configuration: * Change the mark type to Shape (Section 7.2.7).

• Add Compare to Last to both the Color and Shape tiles.

• Compute Using: Right-click the field and select Compute Using > Table Down (Section 10.4) to ensure the comparison aligns with the date hierarchy.

• The Placeholder: Create a Placeholder Field by typing min(1) directly into the Columns shelf to control horizontal alignment (Section 3.6.4).

Phase 3: The 100.3 Three-Month Average

• Average Logic: Create a calculated field 3 Month Average Cnt of Orders using the WINDOW_AVG function to include the current and two previous months.

• Filters: Add the Date Lookup Filter to the Filters shelf. Because it uses the MAX() function (Section 11.8.2), it allows you to filter the view to the most recent month without filtering out the underlying data needed for the average (Section 8.7.2).

• Formatting: Right-click the average field, select Default Properties > Number Format, and set it to Number (Custom) with 0 decimal places.

Phase 4: Final Dashboard Assembly

• Worksheet Integration: Combine the four worksheets: 100.1 Title, 100.2 % diff, 100.3 - 3 mo avg, and 100.4 Current Month.

• Containers: Use Horizontal and Vertical Containers to control the placement and alignment of the worksheets.

• Formatting: Set Row Dividers and Column Dividers to None in the Borders tool (Section 12.6.2) to create a clean, modern look.

• Validation: Use the two validation worksheets created during the build to ensure the Month-over-Month and 3-Month Average calculations match your raw data.