cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dplex Frequent Visitor
Frequent Visitor

Aggregate daily sales data to monthly

I got daily sales data from the last three years (1M+ rows) in total. Through Power Querry I have joined/appended all the data so it's ordered by date. However, I am unable to convert daily sales data into monthly totals. I want to aggregate the data so that it occupies much less rows (for performance). 

 

I already tried Groupping and Pivot  Table options, but no success. Also I do not considered them as an option as I need to automate it. 

 

The idea is to have AGGREGATED monthly data every month and copy/paste in a database (less space and faster performance). 

 

I am sure there is some option for it. I tried Pivot and Unpivot...but I did not get it. Please I need suggestion/advice.

 

Thanks for your support.

SalesData.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sokg Established Member
Established Member

Re: Aggregate daily sales data to monthly

1. Import your files from folder to Power query

2. Create a Revenue Column (which is qty * price)

3. Create a Year column (from Date)

4. Create a Month column (from Date)

5. Merge Year - Month to a new column (Year - Month)

6. Right click on Year - Month column and choose Group by..

 

Capture.JPG

 

Then click ok.

 

Capture1.JPG

 

Is this what you want???

4 REPLIES 4
sokg Established Member
Established Member

Re: Aggregate daily sales data to monthly

Do you get this data from excel???

How offen do you get this data??? (daily, monthly???)

Do you want to import these data to Power bi??

dplex Frequent Visitor
Frequent Visitor

Re: Aggregate daily sales data to monthly

I get them to my email an excel file attached. All the Excel files have same format.

I get them every day at morning time.

I would like more to aggregate data then take it to Excel (throught BI or Power Pivot) to create a separate aggregate Database.

 

For now I am using Connection to folder and loaded data in Pivot  Table. But due to hige number of files, it becomes very slow when updating with new daily files. So my idea was to have monthly files instead of daily.

 

sokg Established Member
Established Member

Re: Aggregate daily sales data to monthly

1. Import your files from folder to Power query

2. Create a Revenue Column (which is qty * price)

3. Create a Year column (from Date)

4. Create a Month column (from Date)

5. Merge Year - Month to a new column (Year - Month)

6. Right click on Year - Month column and choose Group by..

 

Capture.JPG

 

Then click ok.

 

Capture1.JPG

 

Is this what you want???

Community Support Team
Community Support Team

Re: Aggregate daily sales data to monthly

Hi @dplex,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.