 Hello everyone in this tutorial. We're going to be looking at how to create a cumulative calculation using DAX in Power BI So currently if we look at our data We've just got the single table called sales data and within that as you can see on the left We've simply got two columns one is every all the date in the last year And then also a sales amount for that particular date If we go back into our report you can see we're currently able to put that on month So we can see for each month. What was the total sales and what we really want to do is to Present this in a cumulative chart So on the far right of the chart we can see our current total and then obviously for everything else We can see how that has been accumulated over those those period of months. So to do that We just now need to add a few more Tables and we're going to just jump straight in so if you're going to model in and do new table We're going to create a calendar table. So this is allows us to use this as our central table It's not only important for this scenario But it's a great good really good practice and useful in all of your reports Especially if you're trying to join multiple data sets based on date So you can use two options. So we've got calendar or calendar auto calendar auto We'll just automatically generate your calendar based on the dates within your report But I like to have a bit more control and use calendar you can Provide static start and end dates But for us, we're simply going to drive these off of the date column in our sales data So our start date will be the min date within the date column So let's go to sales data and then the end date will be the opposite Which is going to be the max but again of that same column. So sales data there Close our brackets and enter and then what we'll now have is our calendar table Which is basically a replica of our date table here, but for the starting. We've got this is what we need The next thing we need to do because we've now got more than one table is to make sure we identify that relationship in the model So if you go into your model tab down here, all we simply need to do is just connect the date from calendar to the date in sales data And we can see that those two have now identified that relationship So let's go back into report and we can now continue with building our actual measure So if you've seen our previous videos before you'll see we like to capture all of our measures in a separate table Just for easy way to organize so all I'm going to do is create a new table and I'm going to call this underscore measure and The reason for adding an underscore at the start there is just to ensure that alphabetically It will appear at the top of our tables list in our fields pain down the side here So I've called it measure but actually want to call it measures. I know it's not really a big difference But it will help just to make things clearer So we've got measures there So what we need to do is as soon as we add our measure We'll be able to delete this column and it'll convert this to a measures table But for the time being let's just go new measure and we can crack on with doing our calculation So for us, this is going to be a mount and it's going to I'm going to call it C U ML for cumulative and We'll be using the calculate function that's too bad to do this so the first thing to do of course is add the word calculate and Then the expression that we want to do is a sum of our field amounts or sales data amount and In order to do this community over the period of time We need the following filter expression which is based on our newly added calendar table So for us, that's going to be date and making sure we select it from our calendar date, which is now appearing at the top here less than or equal to the max Calendar date Okay, and if you close brackets and that completes our function So what this is doing here is it's basically saying when we do our cumulative calculation We want each month or date period. We want to calculate everything that has less than or and up to our Max kind of date. So when it comes to working on a chart if you're into say in March 2022 what it would do is do the cumulative sum of everything that was less than or equal to March 2022 Likewise when it gets to April it'll do the same But now it'll go up until April 2022 to give you that cumulative picture all the way until the end Which will give us our final max value. So now that we've got our calculation of the right-hand side here We can now delete this column number one just to tidy up this table simply be going delete from model and You'll see this table icon here has now changed to a measures icon So again just makes it all nice and tidy and keeps everything together. So what we can do here is we'll simply copy this Chart we've got here Actually, no, this let's start a new one So what we'll simply do is add a line chart Just so we can do this from the start and line that with our current existing one So this time I'm going to take our date field from our calendar column and put it into our x-axis and Then I'll take our amount cumulative and put it into our y-axis and there you go You can now see how this looks like we're using our cumulative Calculation and we can see that this is doing exactly what it should do as we can see cumulatively We've got a sum all the way up here until March when they've got until February, sorry And then we know that in March there was zero amount So we expect to see a flat line there and then from April we start to see a gradual uptick Now the data we've used to do this is all randomly generated Hence why you see this more or less straight line But of course if you're using real data you'd expect to see more of a sort of maybe a stare stare pattern as your data Gradually builds up over time So I hope that answered your question if that's what you're searching for or you've just learned something new An available functionality you have using DAX in Power BI if you did enjoy the video Please don't forget to hit that like button and if this is your first time watching one of our videos Or you've seen in the past and still yet to subscribe Could I please ask you to hit that subscribe button and also that Bell notification button? So you are notified of all of our videos as they come out in the future So thanks again for watching and we'll see you in the next video