cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sureshponnuru
New Member

Aggregate Monthly Data from Week ending values

Hi Everyone,

 

I have the below master table which has Week Ending Date and sales values. I want to aggregate the week ending sales to month ending sales.

 

Original Table

Week EndingActual Sales
08-03-2020             27,949
15-03-2020             32,142
22-03-2020             34,638
29-03-2020             22,049
05-04-2020             25,168
12-04-2020             30,000

 

Expected Output

March 2020                        1,24,097
April 2020                            48,299

 

I am facing issues with splitting the sales data where the week has an overlap between 2 months. Could anyone help me with the logic of splitting the sales data and aggregate by month? Thanks.

 

Regards,

 

Suresh

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @sureshponnuru ,

 

I wonder how you get this:

March 2020                         1,24,097
April 2020                             48,299

25168/7*5+30,000 = 47977

This doesn't make sense.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
lbendlin
Super User
Super User

@sureshponnuru There is no logic.  Weeks and Months do not go well together. You need to find a procedural solution.

 

- give all the data to the new month

- give the data proportionally based on the number of days of that week in each month

- give all the data to both months

- exclude the data altogether

 

and so on - endless options.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors