3: Building a Data Model to support self-service reporting
3.11 Introduce CALCULATE
3: Building a Data Model to support self-service reporting
3.11 Introduce CALCULATE - Video Tutorials & Practice Problems
Video duration:
4m
Play a video:
Video transcript
<v Instructor>In this sub-lesson,</v> we're going to introduce you to probably one of the more widely-used DAX functions, and that is the CALCULATE function. We're gonna define its purpose and then we're going to use it in demo syntax. Down below, you can see the generic syntax for CALCULATE. It takes an expression, and then you can apply any number of filters to that expression to modify its result. Let's go ahead and get our desktop file open that supports this sub-lesson. We'll go ahead and click on the L_03_12 Power BI desktop file to get ourselves into the Power BI desktop file. We'll wait a moment. The desktop file's now open, so we're gonna go ahead and build ourselves an additional DAX expression as a measure here. We're going to create a measure that is able to calculate our prior year's total sales. The way we're gonna do this is we're gonna use the CALCULATE function. We're gonna take advantage of our total sales measure, and we're gonna manipulate the filters around it by asking it to go one year back and find the sales for the same period last year. Let's go ahead and get ourselves into the formula bar so we can go ahead and create this expression. What I'll do here is, I'm just going to open up the Fields panel a little bit more here. What we'll do is we'll right-click on top of Sales, and we are gonna say New measure. Once again, we'll let the formula bar pop up. I'll go ahead and expand this so we can actually see it a little bit better. Now we have, it says measure equal. Keeping in mind once again, we wanna go ahead and get rid of this here. This is just reminding us that we asked to create a measure here. What I'm gonna do here is I'm just gonna go and type in PY for prior year, and I'm gonna type in Total Sales. Here now, I'm gonna say it is equivalent to the CALCULATE, so I'm gonna use the CALCULATE function. The expression I'm gonna use here is the total sales. I'm gonna go ahead and hit the open square brackets. Then we'll see we have the three measures here, and then choose total sales. And then what I wanna do here is use the DAX function called SAMEPERIODLASTYEAR. If I type in SAME, I'm gonna get that. What I'm gonna do here now is, it's gonna ask me for what is my date field in my date table. I'm gonna go ahead and type order date, like so, and then just scroll down a little bit lower to that one right there. Hit the Tab key, close that bracket off. Let's close the second bracket off so the CALCULATE function closes. Go ahead and Enter. Now we have a measure that is gonna enable us to calculate our prior year sales, so let's go ahead and use that. Got the visual highlighted. I'm gonna take my prior total sales and drop it on the visual like so. I'm just gonna move some of these columns over a little bit more so we can make a little more real estate to take a look at what we have here. Let's just go ahead and do that. I've got my columns have been re-ordered here. Let's just go ahead and get these all into the proper order again. What we can see here is, if I just maybe scroll down a couple rows here, and maybe make this visual just a little bit bigger, is, let's go ahead and take a look at January of 2018 here. I'm just gonna go to show January 2018 on the screen here. We can see the total sales for January 2018 was $2.5 million, little over 2.5. If we go down to January of 2019 here, we can see that the value is exactly the same. The exact same value shows up down here. This one right here was actually able to go backwards one year and say right. Even though my filter context is January of 2019 and I'm running the total sales for this, we use the modifier to go through and calculate out the same period last year sales to give us this value over here. The nice thing about this is this is gonna allow us to start building some ratio calculations so that if we wanted to go ahead and see what our percentage change in a year-over-year basis was, we could go ahead and do that. A really simple introduction into the CALCULATE function. As I mentioned, this is one of the more widely-used DAX functions. This brings us to the end of this sub-lesson.