Calculating Running Totals in Power BI Using DAX

[Music] everyone Sam if I hit today I am going to show you how you can calculate running totals inside of power bi now there's actually a couple ways you can do it so I'm going to show you both ways but this is ultimately what what what we won't get as result so what I've done is I've got your most my sales by date and I've created a 30-day running total and a 90-day running total and then I put them into visualizations and then we can actually change we can check we can change the timeframe and our running total is going to show cases of quite a compelling and so right you know especially if you're you know you're looking at things like inventory or accounts receivable or something like that you know this is a really good piece of analysis that you can run over the top of that that data and then you can even branch out into more more advanced things like comparing your running totals at one period this is another period soon these different things you can do and it's really awesome technique so I'm going to show you how you can take and achieve this from what we've got here from scratch so ok so starting from a blank blank report basically all I've got is my total sales I'm going to write a measure that gives me my 30-day running total so I want to for any date look back from today back 30 days and then sum up all the sales within that time period within that 30-day time period so I'm going to create an another measure here and I'm gonna call this 30 I'm gonna call this running total running total 30 days go equals and then I'm gonna jump down to another line because we've got a bit of logic to writing here so I'm gonna go calculate and total sales then I'm going to use the filter function because the filter function is a iterating function and basically I want to iterate through the data table to the in to capture to capture these 30 days for the running total so I'm going to filter then I'm gonna go all and then I'm going to remove any filter or any context coming from any a date-date column and in this case I'm getting rid of the context and the date the date column which is what is being derived from here so all gets rid of that context or gets rid of that filter and then I'm going to iterate via this filter function I'm going to sr8 through the entire date table and I'm gonna see if the date is greater than max date minus 30 because that's what's going to get us to the first day of the 30 days and then I'm gonna go mm percent and then I'm going to evaluate what is the date also less than or equal to actually less than or equal to the next date now the max date is just going to be equal to the actual is always going to equal that date here so if you just typed in a measure or or a function that said max a date and you put it against this context it's always gonna be a weight to that day so then I'm going to just close this off and go inter and drag this into my table and you'll see that this will evaluate to a running total now so if I just changed this into a dollar dollar numbers I'll forget and you'll see now that through time this is evaluating thirty days on the run its evaluating from its evaluating from here thirty days back from here from here thirty days back here thirty days back so that's how you get your running total and say you wanted to get a different time frame rule it's the same - it's the same pattern all you got to do is copy and paste so you'll say we might want a nice day something like that then you just change this from 30 to 90 and just like that we've got a running total for 90 days instead and that's obviously going to evaluate to something slightly different because we're looking at sales over along the time flame or or a metric over a longer time thing because remember also you can sub in a different measure here it could be profits it could be transactions it could be customers there's lots of different things that you could that you could implement there now we just want to show you another formula that you can write to achieve exactly the same thing and it's using just a different logic different filtering logic inside of the calculate statements so I'm going to call this the running running total running total in 30 days I just go put alts after alternative and I'm just gonna go calculate total sales and then instead of filter I'm gonna use this function called dates in period like date and all the dates in period yep and then I'm just going to jump down to another column another road here and I'm gonna go into date so it just asked me for the date so what dates in period basically does is it opens up it allows you to put some variables or the variables open up a time period so it's a specific time Intelligence function and you can create or open up any time period that you want you've just got to put in the parameters instead I'm just going to put on my parameters here so it basically does what that filter does it's just a different way of writing it maybe a slightly easier way up I would say and you've got a lot more flexibility so here I'm just going to jump back 30 days and you'll see that you've got all these options that you can select before you can jump back say a month a year so instead of 30 days and maybe we might actually have wanted to go back 1 month and I could have gone - one month but in this case I'm just going to keep it at 30 days and I'm just going to go enter and if I put this next to my other 30 day you'll see that it's exactly the same it's exactly the same as the one that we use in filter it's just another way of achieving this a similar result so some good techniques there some good techniques that you get them for me now all that we need to do from here basically is to turn it into a visualization which doesn't take long and then we can see running total in a visual way and then you can add different filters so say for instance you want to only look at a particular year here so say I want to look at a 2017 easy as that the the the the running total adjusts for whatever other additional context that you put inside your and report page so the habit that's how you do the running total lots and lots of applications for this and you couldn't extend this even further it's pretty exciting how how far you could actually extend that but at the end of the day it's the same techniques the same pattern you can reuse reuse it over and over again

Channel: Enterprise DNA

Views: 94,355

Keywords: Enterprise DNA, Sam McKay, Power BI, DAX, PowerBI, DAX tutorial, Power BI tutorial, Power BI Desktop, Powerpivot, DAX Power BI, Power BI DAX, How to learn DAX, Learn Power BI, Power BI training, DAX training, Data analysis, Powerquery, Power BI for beginners, Excel, Analytics, Dashboards, Data, DAX for beginners, Advanced DAX, Running totals, running total power bi, running total dax

Id: RYU1O-incoI

Length: 7min 0sec (420 seconds)

Published: Tue Aug 08 2017