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.

Region	Country	Location	Daily Sales	Weekly Sales	Month To Date	Year to Date
Asia	China	Hong Kong	100	500	4000	40000
Macau	200	500	4000	40000
Beijing	150	400	4000	40000
Japan	Nagoya	200	300	4000	40000
Okazaki	300	500	4000	40000
Singapore	Kallang	100	300	4000	40000
Jurong	300	500	4000	40000
Middle East	Dubai	Dubai	400	600	4000	40000
Abu Dhabi	100	300	4000	40000
Saudi Arabia	Jeddah	150	400	4000	40000
Riyadh	500	600	4000	40000

<tbody>
</tbody>

 

[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.