Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
@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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |