1. Forecasting types of energy consumption - DAX
In this scenario, we continue from the previous example, where we are a data analyst for an energy company. We want to move beyond the built-in forecasts that Power BI creates, and want something that we can customize based on our own industry knowledge.
To do this with DAX, we can leverage average values across our dataset. In this case, we are going to take existing measures that we have to capture the first and last years of data. Using those, we can calculate an effective change on a per-year basis for our data points. We can do this by following a simple calculation from our algebra days. We will take our last year of consumption, and will subtract the first year of consumption value. This calculates our total change for the time period in which we have data. Then, to make this value on an annual basis, we will divide by the total number of years in which we have data, which is three in our case.
Consumption Slope = ([Last Year Consumption] - [First Year Consumption]) / 3
From there, we can start to build out a forecast. The idea with this calculation is to use the slope and project the expected change into dates in the future. We need to capture the last value of data we have, which we have already done with our Last Year Consumption measure. We also need the number of periods that have transpired since then, which can be calculated using a DATEDIFF function from our last data, which is 12/31/18, and then use a SELECTEDVALUE of our Date field. This will dynamically calculate a forecast for each value on a visual without us having to hard-code specific values. Then, we can finally return our forecasted value, which we only want to calculate for days in the future (i.e. have a date value after the end of our data. Using our lastConsumption variable, we can simply multiply our slope and the number of years in the future that data point exists.
Renewable Forecast =
var lastConsumption = [Last Year Consumption]
var numYears = DATEDIFF(DATE(2018,12,31), SELECTEDVALUE('Calendar'[Date]), YEAR)
return IF(SELECTEDVALUE('Calendar'[Date]) > DATE(2018,12,31),lastConsumption + [Consumption Slope] * numYears)
When we plot the historical and forecasted data using a date hierarchy, we can see that our forecast can project renewable energy consumption into the future. At first glance, the data behaves in a seasonal manner, just like we would expect based on the historical data we have. Note that this calculation does not provide an error bound, so the forecast should not be considered incredibly accurate. We can also see that, while we calculated our forecast based on an annual average, Power BI is intelligently applying the forecast to match our data (i.e. at a daily level).
This was a quick example on how to use historical data to forecast into the future. Now, let’s use similar strategies to our financial stock data. Let’s practice!
2. Let's practice!