Reported it just comes down to having the correct start which involves a little tiny setting let me show you okay so this is my data in excel i have information about salesperson order date.
And order amount now i'd like to take a look at order amounts by sales person for each single month i could easily do that with a pivot table right this is an official excel table it's.
Called table sales so i'm just going to go ahead and insert a pivot table from table range let's go ahead and insert it on a new sheet.
I don't have month here but i have order date so if i grab date and drag it to the rows take a look at what automatically happens here my dates are split into years quarters.
To have this default applied if you don't want this default to apply you can go ahead and quickly press ctrl z to get it reversed now i'm just going to press ctrl z a few times to go back to where.
We were now if at this stage i press ctrl z it's going to grab the order date right so check this out i'm going to go drag it here it's going to do the split i'm.
Going to press ctrl z and it's going to ungroup now if you want to ungroup later and you forget about the control z thing you can you just go right mouse click on your dates and go ungroup and you have.
Your dates back if you want to have a different grouping to what excel gives you you can go and group your dates and select the type of groupings that you need so remember before i got quarters.
If i don't need quarters i don't have to select it so just go and click on years and then let's go with days as well and then click on ok and now i have days months and years so.
Posts Related:
Going back to the type of analysis that
We wanted to do i want to take a look at the salesperson so i'm going to bring salesperson as a filter and let's add order amount as my values okay let's.Adjust the number formatting as well because this doesn't look so neat use a thousand separator and zero decimal places okay so this way i can take a look at.
All sales people or i can select multiple or single sales people and have a look at their sales okay so so far so good but what if i didn't want to have the dates visible like this what.
If i wanted to see jan 2021 right and then feb 2021 and then for this one i want to see jan 2022 feb 2022 and just get rid of the.
Year right now i can't do this because whatever i type in here is replicated here if you want to be more flexible with the date grouping you have to use a.
Slightly different approach which i'm going to show you in a second now another limitation with the pivot table report is that i can't get the dates where i.
Didn't have sales so let's kick out the months here and bring over the days here i can see that amy had sales on the 16th the 28th 29th but what if i want to get the list of days in january where she.
Didn't have sales it's going to be a bit difficult for me to calculate that here so if you want a lot more flexibility with the dates in your pivot tables you can use this approach that i'm going to.
Show you right now okay so first of all i'm going to go ahead and delete this sheet and we're going to start off where we were with a slight difference so let's.
Go ahead and insert a pivot table from
Table range now instead of just going ahead and clicking on ok i'm going to place a check mark and add this to the data model.And then click on ok now what changed everything looks the same if i go and grab order date and drop it in rows i get that grouping again where is the.
Flexibility well the flexibility is in the data model and the ability that you can add your own calendar table so the moment you add this to the data model you don't want to do this you.
Don't want to grab order date and bring it into here instead what you want to do go to the data tab and bring up the data model which is this button right here it's the powerpivot window when i open.
That we can see our data set added to this window as well now one thing i'm going to do is adjust the formatting of this so when i create my pivot tables i don't have to correct the number.
Formatting so i'm just going to select that and apply the currency format now optimally if you're using the data model and doing different types of analysis you want to create measures but for just.
Quick and simple analysis we don't really need to worry about that now here's the magic of the data model if i go to design date table i have the ability to add a brand new.
Calendar table to my model so take a look at what happens all of this is being automatically generated i get a list of dates so a new row for each single day.
Of my data now the way this works is that it takes a look at the smallest date that i have here and then it creates a list of dates starting from 1st of january of the smallest year all.
The way until the end of december of the largest year then it adds all these columns now notice i have that month year combination here and if i have different types of grouping of years or.
If i have different fiscal periods i can add my own columns to this as well now there is a single step we need to do before we can successfully use this and that's to create a relationship between.
The calendar table and our sales table and an easy way to do that is just to switch to diagram view so we can see everything a lot neater let's just drag this to the side here and we're going to.
Click on date drag it and connect it to order date that's pretty simple right now just to make sure that i don't mistakenly use the order date and drag it down like i did before i'm going to.
Right mouse click and hide this from client tools this way i'm going to be using the calendar table and not creating those automatic groupings anymore okay so we've done our major.
Setup we're just going to go back to our pivot table here and when i switch to all i can see the calendar table and my sales table notice that the date is now hidden so i'm going to click and drag.
The order amount here which comes as perfectly formatted let's put salesperson in the filter and now let's open up the calendar table and take what we need if i wanted to.
Look at the year and month combination i can do that if i want to add the day of the week as well i can do that as well let's just switch these around now i can.
See which day of the week had the highest sales in january and remember those additional groupings that we had so if i want to see the month and year combination i can click and drag this.
Down and i have the month and year combination now this unfortunately is sorted based on alphabetical order which i don't want so i need to adjust the sorting of this.
The way the sorting works is that you have to correct that in the data model so if we switch back to the data model here and if we go to our calendar table let's go to data view in the calendar.
Table here we need to adjust the sorting in this view and there's a trick around this and that's to use sort by column so you can sort a single column by another column now if we take a look at our data.
Set by which column can i sort this by i can't sort it by month number because that's going to ignore the years and i can't just sort this by years because.
That's going to ignore the month so it helps here to add a helper column that gets this done so let's go to this last column here at an equal sign and use the information we have from year and month.
To create something that we can easily sort one way is just to take our year so i'm just going to click in there to get the column header multiply this with a hundred and add the month now when i.
Press enter i get my values here let's call this yyyy mmm now we're going to go back to this column.
Sort by column and sorted by the new column that we just added and click on ok so check this out the moment i switch to excel.
Everything is automatically sorted now what about getting a list of dates on which we didn't have any sales well let's bring this up to the filters i'm just going to go and select.
A single salesperson and let's go with january 2021. okay so let's bring the date column from our calendar table to the rows now and again just like with our pivot table the.
Standard view is to see the rows which have values but remember in our data model we have a new row for each single day even if we didn't.