I want you to increase your efficiency and to stop using spreadsheets for every single analysis.
Everybody works with time series data at some point in time. Year over year (also known as YoY) analysis is one of the most useful analyses you can perform to determine changes, analyze growth and recognize trends in quantity on an annual basis.
Unfortunately, most data preparers are used to performing some unaesthetic flavor of this analysis using only Excel (looking at you FP&A). Without the benefit of using visualization to easily recognize trends, data consumers are forced to work harder to tease out the most salient information.
If you have access to Power BI Desktop (available for free), then you can perform a tabular year over year difference calculation and then tie that information to a bar chart that will help you visualize the variances.
In this video I will show you how to create a calendar table in DAX (Microsoft’s formula expression language) and use that table to enable a year over year analysis of customer orders at fictional Stark Industries. You don’t need to be an expert in DAX to take advantage, just type in the date calendar formula you see in the video and tweak the simple calculations to fit your data.
You could obviously perform a simple YoY analysis in Excel, but I want you to stay relevant and learn something new!
If you find this type of instruction valuable make sure to subscribe to my Youtube channel.
In this video you will learn how to filter the top N values shown in your bar chart visualization using a slicer.
- This technique uses one measure that generates a number 1-10, that will be applied to a slicer.
- Another measure will basically rank all of the values associated with your data bars and only return the values that are less than or equal to the number you select in the slicer.
The comments that I apply to the DAX function should help make it easy to understand. I have to give a shoutout to GilbertQ from the PowerBI community for coming up with the initial approach which I tweaked for the video.
As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel!
In this video I’ll demonstrate how to use the forecasting analytics option in Power BI. Although Power BI’s forecast algorithm is a black box, it’s more than likely using exponential smoothing to generate results. At a very high level, exponential smoothing is an algorithm that looks for patterns in data and extrapolates that pattern into the future. To help exponential smoothing perform at an optimal level, it is very important to pick an accurate seasonality estimation, as this will have an outsized effect on the time series forecast.
If your data points are at the daily grain, then you’d use 365 as your seasonality value. If your data points are at a monthly grain, then you’d use 12 as your seasonality value. Generally, the more seasonality cycles (e.g., years) that you provide Power BI, the more predictive your forecast will be.
Without giving away the whole video, here is a pro and a con of using forecasting in Power BI.
Con: As I stated earlier the exact algorithm is a black box. Although based upon a Power View blog post, we can reasonably assume exponential smoothing is involved. Furthermore, the results cannot be exported into a spreadsheet and analyzed.
Pro: The ability to “hindcast” allows you to observe if the forecasted values match your actual values. This ability allows you to judge whether the forecast is performing well.
Check out the video; I predict you’ll learn something new.
As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.
In this latest video, I’ll explain how to use a handy DAX function in Power BI in order to group dates together for reporting. We’ll examine a dashboard that contains fields corresponding to purchase item, purchase date and purchase cost. We’ll then create a calculated column and use the SWITCH function in Power BI to perform our date grouping on the purchase date.
Watch the video to learn how to group dates into the following aging buckets, which can be customized to fit your specific need.
- 0-15 Days
- 16-30 Days
- 31-59 Days
- 60+ Days
If you are familiar with SQL, then you’ll recognize that the SWITCH function is very similar to the CASE statement; which is SQL’s way of handling IF/THEN logic.
Even though we’re creating a calculated column within Power BI itself, best practice is to push calculated fields to the source when possible. The closer calculated fields are to the underlying source data, the better the performance of the dashboard.
The humble bar chart is the heart and soul of any visualization tool and is the most effective way to compare individual categorical values. We as humans are very adept at detecting small differences in length from a common baseline .
To quote the Harvard Business Review , “The ability to create smart data visualizations was once a nice-to-have skill. But in today’s complex business world, where the amount of data is overwhelming, being able to create and communicate through compelling data visualizations is a must-have skill for managers.”
If you’re going to start learning a new visualization tool, there is no better place to start than with bar chart basics. In this video I will share how to place a “percent of total” measure (i.e. value) on a Power BI bar chart. We’ll also briefly touch upon customizing the chart’s diverging color scheme.
Since Microsoft is basically giving away Power BI Desktop for free, it may become as ubiquitous as Excel. Don’t be left out!
 Cotgreave, A., Shaffer, J., Wexler, S. (2017). The Big Book of Dashboards: Visualizing Your Data Using Real-World Business Scenarios. Hoboken, NJ: John Wiley & Sons, Inc.