The other piece of functionality concerns the time period selection. The example I give here is, let's say we're interested in Period 9. Maybe we've produced the Period 9 accounts and we're now presenting them. We may want to show just Period 9 itself, just for one month. In which case, we'd need to unselect all these periods and then select Period 9. But let's say we then want to show Period 9, QTD - we'd also have to select Period 7 and 8 so that we have all three months (because this is Quarter 3) selected.
Now, let's say we want to show Period 9 YTD, we'd have to select Periods 1 to 6 as well so that we've got nine periods selected. If we then want to go back to showing just one period, we'd have to unselect them all etc. What would be really great is if we had a third slicer here which allows us to define whether we're showing just the period, the QTD, or the YTD. We can do that.
I'm going to start here by creating a new table and we're going to call this table Time Period Selection and the column will be called Time Period. In here we're going to have the values Period, QTD, and YTD. After that, I'll go to Load.
We want to add a new Slicer in, but first off I'm just going to go to my period slicer here and make an adjustment to the selection controls. I'm going to turn off Select All and I'm going to turn on Single Select so we can now only select one period at a time.
I'm going to reduce the size of this plan slicer a bit and then make a copy of it. I'm going to remove Plan and I'll just move this down slightly. Then, I'm going to go to my time period selection and drop this in. So now, we have the selection of Period, QTD, and YTD.
Now if I move this over here and then reduce it, so we can now select the actual period itself then whether it's Period, QTD or YTD. And then whether it's versus Budget, Forecast 1, Forecast 2, Forecast 3 or Prior Year. Next, I'm going to create a table so I can demonstrate what I'm going to do with the DAX.
In it, I'm going to drop in the P&L line item. Now, we're showing our P&L line items alphabetically there but of course if we wanted to show them in order of the P&L, we can go back to the P&L line item here and go to Sort by Column, then the Account Code. We can see we're now in the order of the P&L.
Now I'm going to drop into this a measure. So, I'm going to bring in our Amount Actuals measure to this table. So, we've now got the Period 3 actuals for Net Revenue, COGS, etc. And if I was to change this to Period 4 then we'd get the figures updating.
Now, I'm actually going to rename this measure so I'm going to call it Actuals Period and of course that will update any reference to this measure in the other measures. Then, I'm going to create a new measure here. And I'm going to call this Actuals QTD and we're going to use a function called TOTAL. You can see we've got the choice of TOTALMTD, TOTALQTD, TOTALYTD. So we're going to use TOTALQTD. I'll tab that and we need now an expression and some dates. Here, I'm going to reference the Actuals Period measure and then for dates, I'm going to go to the Timeline and choose Date here, and I'm going to close that bracket.
Then, I'm going to bring this into our table and I'll format that. What you can see here is we're actually showing three months worth of revenues and costs or actuals. So we're showing Period 4, 5 and 6. So, if I selected Period 4 then you'd see both these show the same amount. If I select Period 5 then the Actuals QTD will be this 5.3 million plus 5.7, which comes to roughly 11. That's what we're saying’s the QTD. So, if I select 6, then we're now showing another 5.3, which takes it to 16.3 million. Now if I was to select Period 7 then we're showing Period 7 here and then the Period 7 QTD which is just one month, so the same thing.
So now I can, of course, create another measure which is going to be very similar to the one we've just created. If I go to New Measure here and then paste that in, I can just change this to YTD and then change the function to TotalYTD and press enter. I can now bring that into our table as well and then I'll of course format that.
So now, if I go to Period 1, all the columns show the same thing. Period 2 QTD and YTD of course show the same thing, as we will also see in Period 3. Then when we get to Period 4 - we show Period 4, then we're effectively showing Period 4 again and here we're showing Period 1 to 4. So, these TOTALQTD and TOTALYTD functions are really useful time intelligence functions and they're going to form the basis of how we use this functionality.
So we've now got three measures. If I just change this to Period 5, we've got Actuals Period which gives us the value just for Period 5, we've got Actuals QTD which gives us the values for Periods 4 and 5, and then we've got Actuals YTD which gives us the value for Periods 1 to 5. Now, that's great, but in my reports, I don't want to show all three measures. I just want to show one measure and return the amounts depending on whether I select Period, QTD or YTD, because right now, this slicer isn't doing anything.
So, I'm going to have to create a new measure and in it, I'm going to use a variable and I'm going to reference these three measures to give me the result I want. So, to create a new variable, I'm going to go to New Measure here and I'm going to call this measure Actuals Variable. It's going to equal, and then I'll start a new line. I always start a variable with VAR, and then I put the variable name. So, I'm going to put the name, Timeperiod, and then equals to start a new line. Now, I need an expression which is going to set the value of the variable. I'm going to use the SELECTEDVALUE function. In it, I need to reference a column name. I'm going to reference Timeperiod from the Time Period Selection table, and I'm going to close the bracket there. The value I filtered the Time Period Selection slicer for is essentially what we're going to get here. It's going to be either Period, QTD or YTD.
I'll then start a new row and I put RETURN. Then I'll start another new row and I now need to enter an expression which is going to reference the variable. I'm going to use a SWITCH function which we saw earlier in the course. I'm going to start with the value TRUE, as we used earlier in the course as well. Then, I'm going to reference the Timeperiod variable equals then “Period". If the variable does equal Period (remember, the variable is essentially this value here), so if we've selected Period in the Time Period Selection slicer, then the variable becomes Period. So, Period equals Period. If that is the case, then I want to return the Actuals Period measure.
I'll start a new line and I'm going to reference Timeperiod again. If this time equals QTD, then I want to reference the Actuals QTD measure. I'll reference Timeperiod again and if it equals YTD, then I'm going to reference the Actuals YTD measure. Finally, if it doesn't equal any of those things (which I think will never happen), I'll just put “N/A” and then I will close the SWITCH function there and press enter.
Let me just recap what's going on here. We're starting with a variable name here, Timeperiod. We're setting the value of that variable with this expression. This expression essentially takes on the value of whatever we've selected in our Time Period Selection slicer, which can either be Period, QTD or YTD. If we select, for instance, QTD, then the Timeperiod variable equals QTD. We then go into an expression which references that variable. The expression we're using is a SWITCH function expression, and the first part of the SWITCH function is TRUE.
When we then go on to the next lines, if this equals TRUE, then we return this result. If this line equals TRUE, we return with this result, etc. So, the Timeperiod variable here, in our example, we said equals QTD. So, QTD equals Period is FALSE because QTD doesn't equal Period, therefore we skip to the next line of our SWITCH function. Now we have QTD equals QTD and that's TRUE, and we're looking for a TRUE. Therefore, we return the Actuals QTD measure.
That's essentially what this Actuals Variable measure is doing. It's just returning one of these three measures based on the selection we've made in the slicer, either Period, QTD, or YTD. Now, let me demonstrate this. First off, I'm going to change this to a whole number with a thousand separator. Then, I'm going to select our table and with my Actuals Variable, I'm going to move it into our P&L Actuals table.
I'll then drop it into our table down here. What I'm showing in this Actuals Variable measure is essentially this Actuals Period measure because that's what the variable is returning. If I change this to QTD, then you can see now this number equals the Actuals QTD measure. If I change this to YTD, it now equals the Actuals YTD measure. This gives me the freedom to pick any period I want, and then define whether I'm going to show Period, QTD, or YTD. That's great and it gives us a lot of flexibility around how we show our actuals. But of course, I want to do the same thing. I want to be able to show the same thing for our plan amounts - so either Budget, Forecast or Prior Year. So, I'm going to repeat the process for Plan.
I'll start with our measure here, and I'm going to rename this Plan Period. Then, I'm going to take the formula here, copy it, and go to this measure table and create a new measure. I'll copy this in and change this to Plan QTD, and then this will be Plan Period. I'll format that.
Then, I'm going to create another new measure here. I'll paste this in again, and this time I'm going to call it Plan YTD. I need to change this to TotalYTD, and then we'll change this to Plan Period again. Now, if I go to this table, extend this a bit, and I'll drop those in. So, we've got Plan Period, we've got Plan QTD, and then Plan YTD.
If I was to select Period 1, then these are all equal. All the numbers are equal. If I go to Period 2, then we've got Period 2 here. We've shown two periods here, and we've shown two periods here. That should be the same for 3. So, that's Period 3, these Periods 1 to 3. And then if I go to Period 4, that's Period 4, that's just Period 4, and that's Period 1 to 4.
If I was to change this to Forecast 1, all these have changed. That's great, and we can go on and essentially recreate this variable, but for the plan. So, if I go to my actuals variable here and copy it, and then I go to P&L Plan here, and I'm going to create a new measure. I'll paste that in and we'll call this Plan Variable. We can leave all of this the same, but then when we get down to here, we just want to change these measures to Plan Period, Plan QTD and Plan YTD.
It's another example of how easy it is just to recycle these measures to create new ones. If I press enter now and format that to a whole number, I can now bring this measure into our table as well. Great. So, I'm just going to change this to Budget and I'll change this to Period 5. So, we've got all different numbers. In this Actuals Variable measure, we're showing Period 5 here to date. So, it matches the Actual YTD measure here.
In the Plan Variable here, we're showing the Budget for Period 1 to 5, which matches the Plan YTD here. Now, the last thing I want to do here is create one more measure, and that is going to be a variance. I'm going to call this Variance Variable. I appreciate that's probably not a great title for the measure, but I'm trying to keep it consistent with how I've named these measures. I'm essentially saying I'm creating a variance between this Actuals Variable and the Plan Variable. So, this is going to be Actuals Variable minus Plan Variable.
So, we're now showing here a variance between the Actuals Variable, which is 26 million, and the Plan Variable, which is 22 million. This leaves about 4 million of variance. If I was to take these other measures out now and just leave the Actuals Variable, the Plan Variable, and the Variance, then this is great. We can select any Period we want – Period, QTD, YTD, any of the Forecasts, Prior Year, and all these numbers are updating.
So, this is awesome, and it's what we're going to use in our Departmental Opex Cost report.
