Introduction
The Power BI Workout Wednesday team has decided that May is made for DAX! Each challenge this month will require some DAX in the solution. For this week’s solution we are going to practice DAX time intelligence functions, looking at the housing market in the US state of Connecticut.
Time intelligence functions allow you to manipulate data over specific time periods like days, weeks, months, quarters, or years. Have you ever had someone ask you for the year-to-date value? Or how about year-over-year calculations? DAX makes working with dates and times easy!
Requirements
- Get data from the Connecticut Housing Finance Authority using the following API endpoint: https://data.ct.gov/resource/jpi8-zeza.json
(hint: get data from web and pass in the API endpoint URL) - Get National data to use as a comparison from: https://dqydj.com/historical-home-prices/
- Create a date table using either M or DAX
- Set your date table to begin in Jan of 2001 and end in Dec of 2020
- Create relationships between the Date column in your Date table and:
- the Date column in your CT Housing Data table
- the Date column in your National Housing Data table
- Using DAX to create the following measures:
- Median sale price (hint: the source data already contains median by county and year, be careful!) for Connecticut and National
- Year-over-year median sale price for Connecticut and National
- Percent above/below national median home price
- Create at least 2 visuals and a date slicer
- Column chart comparing YoY Median Housing Price Trend
- Table containing Median Home Price and the Percent above/below national median home price
- If you want to build an identical viz, add the sparklines comparing median home price and year-over-year gains
- Answer the following questions:
- Which year did the housing market have the highest year-over-year gain in Connecticut?
- If you’re moving from within the US, when was the best time to buy a home in Connecticut? 😉
Dataset
This challenge uses data from two different websites – the Connecticut Housing Finance Authority’s API Endpoint and Don’t Quit Your Day Job’s Historical Home Prices List. Use the web connector to get data from these two sites, and create your own Date table.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. 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.
Thanks for above interesting Challenge. Learned to create a Date Table using DAX !!