Skip to playerSkip to main contentSkip to footer
  • yesterday
Power BI Magic: Easy 30-Day Sales Calculations Made Simple

Learn how to simplify your 30-day sales calculations in Power BI with this easy-to-follow tutorial! Discover the magic of Power BI and unlock the secrets to effortless sales analysis. In this video, we'll show you how to create a 30-day sales calculation in just a few clicks, helping you to track your sales performance and make data-driven decisions. Whether you're a business owner, data analyst, or Power BI enthusiast, this tutorial is perfect for anyone looking to streamline their sales reporting and take their business to the next level.
So, what are you waiting for?
Watch now and start calculating your sales like a pro!

• Power BI Sales Tips
• 30-Day Sales Analysis Power BI
• Power BI Calculations Made Easy
• Sales Dashboard in Power BI
• Power BI Magic Tricks
• Quick Sales Metrics Power BI
• Visualize Sales Data Power BI
• Power BI for Beginners
• Sales Calculation Techniques Power BI
• Unlocking Power BI Potential

#power bi #powerquery #powerbiservice #averagexpowerbi #businessintelligence #movingaverageinpowerbi #TimeSeriesAnalysis #DataVisualization #rollingaverageinpowerbi #timeintelligence #SalesMetrics #SalesReporting #PowerBIDashboard #BusinessAnalytics #DataAnalytics #PowerBITutorial #DataAnalysis #MeasureinPowerBI #SalesAnalysis #BITools

Category

📚
Learning
Transcript
00:00hey friends welcome back now in this video we'd like to dive deeper into DAX and again it's time
00:11intelligent because we would like to figure out what is the rolling 30-day sales so rolling just
00:17refers to a window so for instance we have 30 days then we would like to know what is the amount of
00:23sales within these 30 days and then if we have the 31st day then of course the first day of our
00:29window gets removed and the 31st day gets added so that's what it's meant by rolling it's like a
00:35rolling window maybe you've heard this term before now let's explore how we can do this in Power BI
00:40so you can see that I renamed my last sheet as scenario analysis and then I click here on a
00:47plus symbol to add a new page so we have a little bit more space so far so good now next we need some
00:54kind of well table structure again I'm using the table because I think it's most easy to explain
01:00and best to understand with this visual but of course as I said before this can be applied later
01:06on to any kind of visual you want okay so let's get started at first I want to have my dates because I
01:13would like to see a rolling 30 day of sales so let's do that let's click on the date here click on date
01:20and then we can see here our date hierarchy in this case I do not like to see the hierarchy because we
01:27are on a daily level this is why I right click here on my date and I say I'd like to see the
01:33individual days so click here right click and date and now we can see here the individual days
01:39now weekday names here are in German that's again based on my current setting but of course for you
01:44they should be displayed in your native language and also by the way we have the option if we select
01:50date here you can see that the column tools here this window appears and there you can also specify
01:56how you want to format that so if you want to have a short date here you can click on short date
02:00and then you would see here this as short dates right without the weekday name it's up to you
02:05how you want to format this but there are various options in here available
02:09so maybe this one as well then it would like look at that right up to you what you prefer
02:15so now we get our dates so far so good next I'd like to see a KPI and in my case you probably know
02:23that I will refer to the measures table and within the measures table I'm going to use the total sales
02:28again but I'd like to mention one more time that you could of course also use total profit total cost
02:34or any other kind of measure you would like to try so let me just tick the total sales or drag and
02:40drop it in here like that and I can see here the individual sales and the goal now is to calculate
02:47a rolling 30-day window so to do this we need another expression so let's create a measure right click
02:54here on the measures table and then we create a new measure so click no measure and then let me just zoom
02:59in so you can see that better on your screens all right so the measure itself we're going to name this
03:05rolling 30 days sales okay like that feel free to choose a banner name that I do here this is not
03:14that important but let's actually get started with the DAX calculation always remember when we need to
03:21change the filter context which in this case we do we need probably in 99.9 percent of the cases we need
03:29the calculate function so this is actually where we get started so we want to calculate something
03:35so calculate now needs the expression which we want to calculate and then the filter which modify
03:41the context in which the expression is evaluated that means that in here I can refer to my total sales
03:49because this is the measure I'm currently using this one and then I'm going to use the filter function
03:56so filter and filter allows us to give it a table and then filter the table by an expression so the table
04:04we're going to use is the dates table and because we want to use each date which we have we use the all
04:12selected function we could also use all function in this case I'm going to use all selected function
04:17and if you want to know what it does one more time it's always helpful to at least read this description
04:23even though sometimes it's maybe not that intuitive what it does but here it turns all the rows in the
04:28table or all the values in the column ignoring any filters that might have been applied and so on
04:33important here is this ignoring any filters because this simply refers to the fact that the current filter
04:39in the visual which is for each row the specific day is ignored and that is the main point so we need to
04:47ignore all the dates we would like to have all the dates so that's why we refer to the dates table
04:51inside the all selection function all select function like that and now we say that we want that the dates
04:59table the date itself so from the dates table the date this one here should be in this case greater than
05:07the maximum of the dates table date so this one here we just select it and we subtract 30 days minus 30
05:17and at the same time power bi as the second argument you can use a double ampersand this is an and condition
05:25the second condition for the table which you want to filter so the dates table is that the dates date
05:32in this case here is less or equal to the maximum of the dates table date this one here
05:41so let me just select it let's close this here and that also let's close the filter function and then
05:48let's close the calculate and after that let's actually select the whole function first ctrl c to copy it
05:55then i'm going to my helpful dex formula tool here i paste this inside and then i let it do its magic
06:03just wait a little bit and here is my formatted code i copy it and then i go back to my report
06:08drag this out let me get this in and i'll paste this inside so let me select it ctrl b to paste it
06:15and then i press the checkbox here and we got our rolling 30 day sales now let's dive a little bit
06:23deeper in what this formula does now remember we would like to calculate the total sales in a modified
06:29context that is why we wrap the total sales inside the calculate function the new context for the
06:35calculation of the total sales is this table which we create here this part the table itself contains
06:42of all the dates ignoring the dates we can see here in the visual and then we make sure that the dates
06:48itself which we want to have for our total sales calculation is greater than the maximum minus 30
06:56days the maximum date on the other hand this one always refers to the current row and this means that
07:02for instance here for the 26th of july 2021 it would be here the fact that the the lower limit should be
07:11actually this date minus 30 days so that's what meant here this is the the lower level and the upper
07:19level this is why we say at the same time the dates table date should be less or equal to the maximum
07:24and the maximum one more time is referring to the current row and by specifying this way let me just check it
07:31one more time we then have a 30 day period like for instance starting here and then 30 days in total
07:37for calculating the value and then for the next day we just shift this window up and we do it for the
07:43next day as well next day as well and this is how we get this rolling effect so to actually put this
07:49inside let's place it here so where is it the rolling 30 days here it is let's just first select it
07:55make it a dollar symbol and also make sure it only has two decimal numbers so we have a nice format kpi
08:03and then let's actually add this to our view let's click on the rolling 30 day sales take this option
08:09and then let me also make it a little bit bigger so let me go inside the view here check the formatting
08:14pane and then go in here under value section and then just choose here a bigger size 14 for instance and
08:20hopefully this you see that better okay now the first thing we could see is now next to the days
08:28where we had sales we now also have the sales oh sorry the dates where we had no created any sales
08:34those are included now in these rolling 30 day sales because this is referring to our dates table
08:39meaning it is not ignoring any dates it's really keeping each day into account that is why this value
08:46here is repeating because it's simply the sum of those two values and then we do not generate any sales
08:51so the value doesn't change and this just means that we now sum up the first two and here it remains
08:57two because actually those are added but those are blank so nothing changes here then we have this one
09:02which is the sum of those then we have this one which is the sum of those including this 451 and so on
09:08and so on it looks like a cumulative sales which you are kind of right if you think that but the
09:15difference now is that it is actually shifting meaning after 30 days and you'll see that that it will
09:21not rise the whole time so for instance here still rising still rising still rising uh and let's just
09:26say here for instance right this one would be example this one as well so obviously here the value is
09:30smaller than this one simply because the first entry of the time frame of 30 days just gets removed
09:37right it could be a very high value somehow maybe this value gets removed right and then the 31st value gets
09:45added for instance value which would be smaller and this is why then these values are smaller right
09:49or in this case no value is added that's why these values are smaller so that's what's meant by that
09:54so hopefully you understood what it does it is a kind of cumulative computation but only for 30 days
10:00and then we when we derive at the 31st day then the 31st day is added but the first day of our window gets
10:07removed and this is how we get the sliding effect and this is what we created here together using this
10:13formula index so these learn rolling 30 days so that's it actually for this expression so i think
10:21that's kind of helpful at least from my point of view because i had this um well this request a few
10:27times that people wanted to see this 30 day sales because it's a let's say rather smoothing sales window
10:34which gives us more insights that maybe just one day where we could have for whatever reason some kind of
10:38outliers right and the great thing here is that you can make this dynamic so the 30 days of course can
10:45be changed right you could say well in our company it's actually we take a look at the 10 days of sales
10:51or one week like seven days of sales anything like that or remember our scenario analysis you could also
10:59start creating a field parameter sorry a numerical parameter on the modeling and then use the numeric
11:06parameter and put it in here and then you have a slider right where the end consumer then can simply
11:12specify what should be the window and based on the selection this window will change until the calculation
11:17will change so that's just an idea which you might want to try out so thanks a lot for watching
11:25um and i can't wait to see you in the next video until then thanks guys

Recommended