Aggregate Monthly Data from Week ending values

[sureshponnuru]

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 Ending	Actual 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

[v-jayw-msft]

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

[lbendlin]

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