Converting Weekly Amounts to Monthly Amounts

[etcbiz]

I have some data that I'd like to convert the weekly amounts to monthly amounts that the amounts actually were earned within that month. I'm able to convert the weekly amounts to monthly amounts by taking the DateTimeMonth for the start of the week but that becomes hard for certain weeks that are in two months. See Example below:

Salesperson	Week Date	Revenue
Joe	1/1/2023	3000
Joe	1/8/2023	2000
Joe	1/15/2023	4000
Joe	1/22/2023	6000
Joe	1/29/2023	5000
Joe	2/5/2023	1000

Technically revenue for January should be: (3000+2000+4000+6000+3000=18,000). But if I use the DateTimeMonth and then cross tab it, the January amount becomes 20,000. How do I go about fixing this?

[Carolyn · Accepted solution]

I would use a Generate Rows tool to get all the days within the week. Then you can allocate based on the relevant days within each month. I was assuming that Saturday and Sunday don't count for the revenue split and Monday through Friday do. However, I'm getting $17K for January, since 1/30 and 1/31 are in January, but it looks like you're counting 3 days, so maybe you're on a Sunday-Thursday week (in which case, you'd just change my Filter tool)