Distribution of Value across Non Standard Calendar Months.

[slimchops]

Could I ask for some help and guidance please. I have this scenario:

I have a list of work orders that are taking place over a defined period of time with a corresponding value. For example:

Order Reference	Value	Start Date	End Date
190193	139880	01/04/2019	31/03/2020
189226	1262.59	26/08/2019	31/12/2019
189699	625.53	06/08/2019	27/09/2019
189157	3631.68	29/07/2019	31/03/2020
188891	3316.12	07/08/2019	13/08/2019
189649	18289.6	19/08/2019	31/12/2019
189531	41889.07	15/08/2019	19/08/2019
187442	13652.15	20/06/2019	31/07/2019
187186	8066.19	20/09/2019	20/09/2019
181827	5845.09	29/07/2019	02/08/2019
187387	8354.16	31/07/2019	02/08/2019

The value needs to be equally distributed across the Start to End Date Range by Month. With my limited skills I can achieve a result into the associated Calendar Months over the order date range.

However, where I am struggling to find a solution is to distribute the value across a Non-Standard Calendar periods as the Client fiscal months are not the Calendar months. They are:

July	01/07/2019	26/07/2019
August	27/07/2019	23/08/2019
September	24/08/2019	27/09/2019
October	28/09/2019	25/10/2019
November	26/10/2019	22/11/2019
December	23/11/2019	27/12/2019
January	28/12/2019	31/01/2020
February	01/02/2019	28/02/2020
March	29/02/2020	27/03/2020
April	28/03/2020	24/04/2020
May	25/04/2020	29/05/2020
June	30/05/2020	30/06/2020

Any steers as to how I should approach getting a report that from the Order start and end date distributes the Order value across and into the associated Non-standard Calendar months?

Many thanks, Andrew

[AnthonyTilley]

not sure if there is a better way to do this but i havea similar situation where by the monthly sales figures run from payday to payday which is always 5 working days before month end.

i found the best option was to create my own date table 

in this table i have the date colunm to join and then a colunm for Working month 

in my example below everything after the 23rd of the month is actually treated as september 

by using my own date table i then look up the working month date and use this date in my distribution 

Date	Working Day	Working month	Work month Text
01/08/2019	TRUE	01/08/2019	August
02/08/2019	TRUE	01/08/2019	August
03/08/2019	FALSE	01/08/2019	August
04/08/2019	FALSE	01/08/2019	August
05/08/2019	TRUE	01/08/2019	August
06/08/2019	TRUE	01/08/2019	August
07/08/2019	TRUE	01/08/2019	August
08/08/2019	TRUE	01/08/2019	August
09/08/2019	TRUE	01/08/2019	August
10/08/2019	FALSE	01/08/2019	August
11/08/2019	FALSE	01/08/2019	August
12/08/2019	TRUE	01/08/2019	August
13/08/2019	TRUE	01/08/2019	August
14/08/2019	TRUE	01/08/2019	August
15/08/2019	TRUE	01/08/2019	August
16/08/2019	TRUE	01/08/2019	August
17/08/2019	FALSE	01/08/2019	August
18/08/2019	FALSE	01/08/2019	August
19/08/2019	TRUE	01/08/2019	August
20/08/2019	TRUE	01/08/2019	August
21/08/2019	TRUE	01/08/2019	August
22/08/2019	TRUE	01/08/2019	August
23/08/2019	TRUE	01/09/2019	September
24/08/2019	FALSE	01/09/2019	September
25/08/2019	FALSE	01/09/2019	September
26/08/2019	FALSE	01/09/2019	September
27/08/2019	TRUE	01/09/2019	September
28/08/2019	TRUE	01/09/2019	September
29/08/2019	TRUE	01/09/2019	September
30/08/2019	TRUE	01/09/2019	September
31/08/2019	FALSE	01/09/2019	September