Power BI DAX: Running Totals Using Variables, CALCULATE, and FILTER

[Music] hey there my name is matthew peterson i'm a trainer at pragmatic work so we're located in northeast florida and i recently had done some trainings with with the company and i had a follow-up question from one of the people in attendance that wanted me to take a look at their data and they were looking to get a rolling total for some sales based on the different agencies that they had and i said oh yeah we can definitely do that uh and i gave the person the the solution they said well that's not exactly what i was looking for so let me just show you what i thought he was asking and then i'm gonna show you what we really want to do for his scenario so if you take a look right here this is the data that was uh basically some dummy data he gave me and he's got the different agencies when the date of a sale was done and then how much money was made from it so not some modest money here and so i was like okay well to do rolling totals there's a dax function uh called uh year to date and it just keeps rolling up your totals and it's really simple to do in dax and so i said well that's probably what that's what i assumed that he would wanted and so if i take a look right here and i'll show you this measure in a second if you're interested from november 1st we have 10 of sales and then when we go to december 1st we have 15. so 25 is the rolling total but then when it got to january 1st it does a reset and i'm like here you go this is great and i'll show you what this formula is right here my rolling total and let me come on in and i'll make this larger and we'll zoom in it's using a um this if is blank is just here so that i don't get blank values uh but this total year to date says i'll take your total sales measurement which i just said sum up the sales column and then you just feed it the dates and dax is super intelligent enough to figure out how to do this in the background and then he said you know what i don't want that i actually want it to continue from the very first initial transaction to the most recent one that we have and so then i had to go back to the drawing boards and go okay well this isn't what you wanted so in order to get the solution that he was looking for and if you're ever looking for finding a rolling total for all time we can make a calculated column and to make it a little bit more fancy here he also wanted it based on the different agency that was being reported on so agency a and then dissect it down to agency b c whatever kind of qualifier you might have here so let's take a look at how we can do this with a calculated column so i'm going to come back over here into our data view and the issue is if i put in a calculated column here and i say let's just sum up the sales table let's just take a look at what that actually does so i'm going to come on over we're going to add in a new column and i'll zoom on in here and make this larger for us and i'm just going to say we'll call this the rolling total and so if i simply say hey i want to sum up what column it's my sales column from the sales table and we close that off and i hit enter notice what's going to happen each time oh i already have a rolling total measure here so let me i'm going to call this the let's close that out let's give it a proper name we're going to call this the rolling total column all right now let's see the results of this and so it reports 72 every single time because it's taking this this is called row this is called filter sorry row level context and so what happens is when it's running this calculated column it's doing it on all of the numbers it doesn't know that it's only supposed to be doing it for you know one for row one or only for row two etc so what i had to do is i had to figure out a way of saying okay when you're on row one i only want you to add for agency a i only want you to add this number 10. but then when we move down to agency a for the second sale here i want you to add the 10 and the 15. and then when i get down to the next one 72 i want you to add the 10 15 and 13. and then when you move to agency b don't worry about those three numbers agency b for the first sale should just be the 18 and you get the picture going forward so how can we do this well what we're going to do is we're going to use variables to return a filter down table return the current date of our row where we're making this column and then add all the numbers that are left in that filter table so let's take a look at how we can do this so we'll get rid of this here and we'll zoom back on in and we're going to make this the appropriate column here so i'm going to use variables because it's just a little bit easier to understand our formulas by using variables so i'm going to say i'm going to use a variable first so just var and i'm going to call this the current date because i want to reference the current date that we are on for every single row and to get the current date i'm going to say the current date is equal to i want it to be look at the sales and look at my date column so now it should reference whatever date is currently going to run the calculated make the calculated column formula and now i'm going to need to do another variable here as well so let's tab this over and our next variable is i also have to make sure i'm only doing it for the agency of the row that i'm on so i'm also going to make a variable called the current agency and this is going to be equal to again we want to reference the agency column all right so now i have uh for every row i will now know what the current date is and what current agency i am on now where are we going to use those next well now i'm going to use those variables to filter a table down so i'm going to call this the filtered table and this is going to equal we're going to use our filter command here so we're going to say let's filter down a table what table do i want well the table i'm doing this on which is my sales table so we got our sales table and now we put in the expression and so what were we really trying to do here well we wanted to always return the results for the date that you're on and any previous dates for also the agency of the row that we are currently on so what i have to do here is we're going to say let's take the sales date so reference the current sales date and i want to return all dates that are prior to or equal to our current date so it's going to look at the whole sales date column of my sales table and it's going to show me just the dates that are less than or equal to the current date for the row that i'm on so we're going to put in here our current date great that's not the only requirement we also want to make sure that we're only returning the agency of the road that we're on so that's why we had to use this current agency so we're gonna go ampersand ampersand that's just like an and statement here and we're gonna go now with you know just to make this a little bit easier i'm gonna shift this down so we can see it all as one and we're also going to say that the agency needs to equal the current agency of my row and that's why we're declaring these variables at the very beginning and then that should filter out my table then the ultimate goal is i'm going to return that filter down table and then just going to calculate all the numbers that are left within that table so now i need to go into my return statement after you declare variables when you want to actually execute a formula to run um to produce a result for you you have to use your return function so we're going to say let's return all right now here's where we run into one more issue and we're going to be using the function called calculate because what calculate does is it will do a calculation for you but you get to say the filter that you want without calculate it automatically uses your row level filters here so we're going to say let's calculate and then we have to give an expression what are we trying to calculate well i want the sum of my sales column so we're going to go up here we're going to go sum and now i need the sales column so sales i'll scroll this down just a little bit sales sales right here and then that is what we want to be doing however here again we want to do it on that filter table that i had declared up earlier so we're going to say do the sales column normally it would just do it on that that whole column but we're saying hey we're going to we're using the calculate function not to do it on the whole call on the whole column the whole table but on our filter down table so now what we can come over here and do is we're going to say the filter is going to be our filter table and we will close that off so it's going to find the sum of the sales column but it's not going to do it for the whole table which is what it currently was doing and giving us these 72s it's going to do it based on our filter table so if all is right in the world and we hit enter we should see our results and they are picture perfect take a look we have 10 for the first then on the second one 10 and 15 make 25 beautiful when we get to our third sale of agency a is adding all the numbers from january 1st and prior because we said the dates are going to equal the current row date plus any previous states and it's all just for agency a when we skip on down to b it starts over again when it doesn't really start over it just does its job it takes the current date which is november 1st brings back and the current agency which is b so technically that brings back that whole table but then we say hey we only want to bring back the dates from here and prior well there's no dates after our november 1st so that's why we're only getting that and so not only does this work right here as the column but what we can now do is if we come back over to the report view and i add this in so now i'm going to take my rolling total column add this n and now we see the results so again i'll get rid of this up here and i'll make this table come down just a little bit so we can actually see it without that formula that rolling total was kind of great because it was a year to date but then we got that issue but here it doesn't take the the it doesn't reset when you get to that new year so by using variables declaring um what we want those kind of filters to be we use those in a filtered table and then we wrapped it into a calculate statement and i can see where this would be beneficial in a lot of use case scenarios so i hope this helps you out if it does make sure to like subscribe also if you are interested in any formal training whether it be live training in person or are on demand learning products take a look in the description below for 20 off coupon and please let me know is there anything else that you would like to see in the future and i hope to see in future videos [Music] you

Channel: Pragmatic Works

Views: 44,006

Keywords: power bi, bi, microsoft, power bi variables, power bi calculate, calculate function, power bi filter, pragmatic works, dax, dax formulas, calculated column

Id: tB_FEqQcmXQ

Length: 11min 32sec (692 seconds)

Published: Mon Apr 05 2021