Exploring AirBnB time series
1. Exploring AirBnB time series
This lesson starts our journey of time series analysis. First, creating a new date variable, then starting to explore trends over different date grains, and finally month-over-month changes. Time series analysis requires a time-based variable. In the Data view, we can see that the glassdoor dataset includes a ReviewDateKey. It seems to be a concatenation of year, month, and day, but we can transform this into a date-type variable using DAX. To do this, click on _New column_ under _Table tools_ in the top ribbon. The DAX formula will use DATE() to create a new date variable. There are three inputs: year, month, and day. For year, we will use the other DAX function LEFT(); ReviewDateKey as the input and extract the first 4 characters. For month, use MID(), again ReviewDateKey, starting at character 5 and extracting 2 characters. Finally, for day, use RIGHT() with ReviewDateKey and extract the last 2 characters. We can see the new date variable, next to the datekey. In the Fields pane, we can see that Power BI automatically created a Date Hierarchy as well for this variable. This provides different date grains or levels of the date, for analysis and visualizations. I'll create a Stacked column chart to show the number of distinct reviews by date. Power BI will initiate visualizations using date hierarchies at the highest level, in this case "Year". There are several options to drill down to lower levels. First, the double-down arrows which drills into each level and aggregates the values at that level. Clicking a couple of times will take me to "Month" and show counts of reviews by month. One more time will drill down to the "Day" level and show counts for each day of the month across all three months, which is useful when looking to answer questions about trends existing within a typical month. Let's move back up the hierarchy. The other option is the split double-down arrows. This will expand each level in the hierarchy into the next level and aggregate. To visualize this, I'll drill down again to the lowest level, day. Here, instead of aggregating at the day of the month level across all months, it aggregates at the day of the month for each month. Finally, you can drill down into specific points by turning on "Drill Mode" clicking on the single down arrow. Then click into the point of interest, for example "2021", "Qtr 1", "March". Here we are aggregating again at day of the month but only for March. We’ve seen how the count of reviews changes across dates, but what about day of the week? We can get to this answer using DAX. We’ll create a new variable called “DayOfWeek”, then use WEEKDAY() with the glassdoor ReviewDate to extract the day of the week of each date. Note, the function will represent Sunday as the first day of the week by default. I’ll create a new Stack column chart using DayOfWeek on the Axis and a distinct count of ReviewId as the Values. More reviews were clearly written on Wednesday, the 3rd day of the week. Does this vary between males and females? Adding the gender variable to Small multiples, will split the column chart into two. The two genders show different times in posting reviews. Finally, another important part of analyzing a variable over time is understanding how it changes between periods, for example from one month to the next. Power BI makes this easy with date hierarchy variables and quick measures. Let’s create a quick measure. Under calculations, choose the appropriate time intelligence option, for example “Month-over-month change”. Drag the date variable to “Date”, ReviewId to “Base value”, and leave “Number of periods” at 1 as we only want to calculate the change from the previous month. I’ll rename to “MoMChange”. On a new page, I’ll create a new Clustered column chart, then drag “ReviewDate” to Axis, “MoMChange” to Values. Drill down to the month level. June shows a massive MoM drop however, that’s because May is the last month with data. There was a 30% increase in reviews in May. Now it’s your turn to start your time series analysis.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.