Expand a table using start and end dates

[Anonymous]

Hi there

I have a dataset that has a start and end date with 3 sets of numbers that I need to work out the monthly amount for and then put that amount in the correct month for each of the three amounts.

For example, row 1 starts on 1 Sep 20 and ends 31 Aug 21. The premium and comm are the upfront annual amounts so I need to populate a Premium Monthly field for Sep 2020 with 4,173.42, same for Oct, Nov etc to Aug 21. Then repeat that for Comm1 Monthly and Comm2 Monthly and then repeat that down the list to the bottom.

The end result would then allow me to run a pivot or cube in say Jul 21 that will return the total Premium, Comm1 and Comm2 that applies to that month. Can M or DAX do this and if so, please let me know how?

PeriodStart	PeriodEnd	Premium	Comm1	Comm2	Months	Premium Monthly	Comm1 Monthly	Comm2 Monthly
01/09/2020	31/08/2021	50,081.06	-	10,016.21	12.00	4,173.42	-	834.68
01/05/2021	30/04/2022	165,021.53	23,103.01	33,004.31	12.00	13,751.79	1,925.25	2,750.36
01/07/2021	30/06/2022	55,680.91	6,960.11	11,136.18	12.00	4,640.08	580.01	928.02
01/07/2021	30/06/2022	12,373.19	556.79	2,474.64	12.00	1,031.10	46.40	206.22
01/07/2021	30/06/2022	171,295.91	7,708.32	34,259.18	12.00	14,274.66	642.36	2,854.93
01/07/2021	30/06/2022	161,189.68	7,253.54	32,237.94	12.00	13,432.47	604.46	2,686.49
22/09/2021	31/08/2022	41,754.61	5,219.33	8,350.92	11.00	3,795.87	474.48	759.17
22/09/2021	31/08/2022	237,962.03	29,745.25	47,592.41	11.00	21,632.91	2,704.11	4,326.58
22/09/2021	31/08/2022	785.41	98.18	157.08	11.00	71.40	8.93	14.28
01/11/2021	31/10/2022	57,557.50	7,194.69	-	12.00	4,796.46	599.56	-
01/11/2021	31/10/2022	33,012.02	4,621.68	6,602.40	12.00	2,751.00	385.14	550.20

[amitchandak · Accepted solution]

@Anonymous ,

For M- Refer if this can help

https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

For DAX

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

Thank you, I will take a look