Converting Weekly Amounts to Monthly Amounts

Question

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:

SalespersonWeek DateRevenueJoe1/1/2023

3000Joe1/8/20232000Joe1/15/20234000Joe1/22/20236000Joe1/29/20235000Joe2/5/20231000

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 Answer

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)