Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.