Power BI Magic: Easy 30-Day Sales Calculations Made Simple

expertiseexcelerate

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

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

Category

Transcript

Recommended