Introduction
This week’s challenge is provided by guest contributor Erik Svensen https://www.linkedin.com/company/catmansolution.
The workout will use visual calculations in combination with numeric parameters to highlight a period in a development chart.
(Please note visual calculations do not currently publish to web)
Requirements
- 1. Retrieve the data from Google Sheets – (you only need orders)
2. Add a calendar table in the model with the CalendarAuto() DAX function and create
calculated columns for Month and Month Name.
3. Crate a relationship between the Order date and your calendar table
4. Add a Numeric Range parameter called “Highlight Months” with 1 as minimum and
12 as maximum
5. Add two measures to this table called Min Month and Max Month – these should
calculated the first and last month number of the user selection.
6. Add a calculated table called Offset with this DAX – Offset = {“Yes”, “No”}
7. Create a measure called Offset ? in this table to determine the selected Value
8. Add a measure in the Orders table called Sales Value that sums the Sales column
9. Add slicers for each of the parameters
10. Add a line chart
11. Add the Month and month name to the x-axis and sales value as the value
12. Add a Min Month, Max Month and Offset ? as Tooltips in your chart
13. Add a visual calculation to check if the month number from the x-axis is between the
min and max month
14. Add another visual calculation to check if the user wants to offset values
15. Turn values on y-axis off
16. Add a measure that calculates the total sales in the highlighted period and add that
as a subtitle to your chart
17. Format the line chart so it looks nice
Dataset
You can find this week’s data in Google Sheets here (no login necessary). You can connect
directly or download the data to your local machine and connect to it from there.
Note: You only need the Orders sheet. The other two sheets in the file are not required.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2024 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko @donsvensen. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.