Newbie needs help

[Jaddy Balagtas]

Hello I am new here and I'm not sure if this question has been asked before. My problem is that I am creating a report for sales. The report has daily sales. weekly sales, month to date sales and year to date sales. My data set is by day. How can I have them based on the column that I need in the pivot table.

[TABLE="width: 579"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Country[/TD]
[TD]Location[/TD]
[TD]Daily Sales[/TD]
[TD]Weekly Sales[/TD]
[TD]Month To Date[/TD]
[TD]Year to Date[/TD]
[/TR]
[TR]
[TD]Asia[/TD]
[TD]China[/TD]
[TD]Hong Kong[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Macau[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Beijing[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Japan[/TD]
[TD]Nagoya[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Okazaki[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Singapore[/TD]
[TD]Kallang[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Jurong[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Middle East[/TD]
[TD]Dubai[/TD]
[TD]Dubai[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Abu Dhabi[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Saudi Arabia[/TD]
[TD]Jeddah[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD]Riyadh[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]40000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Misca]

If you want them all in the same pivot table at the same time you can't do that with normal pivot tables. Or you have to build a funny looking source data table where there's a separate column for each of your values (daily / weekly / monthly / yearly) and even then you have to make sure all the values can be found in the right rows to start with. Not a good way at all. A much better - though still not perfect - solution would be to use several pivot tables and maybe collect the data you want into a single view with GETPIVOTDATA-formulas. If all your pivot tables use the same source data you can use the same slicers to filter them all and you could achieve a pretty nice report that way.

But by far the best way to do this would be using measures in Power Pivot.

The catch with the Power Pivot is you're going to have to build a data model (calendar table and all) and use filters to achieve what you're looking for. Unfortunately it's not that easy to show you how it's done because I'd need to know the data model structure first. But if you spend some time watching Power Pivot lessons in YouTube you can do that yourself in couple of days. And you're going to thank yourself later for doing that.

Here's a good place to start learning Power Pivot and DAX formulas: https://youtu.be/LkDbHRCZ4mY

 

[Jaddy Balagtas]

Thanks I appreciate the help. The other alternative is to process the data and save them to a table so that the data is already ready to show.