Expand a table using start and end dates

Question

Hi there&nbsp;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.&nbsp;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.&nbsp;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?&nbsp;PeriodStartPeriodEndPremium&nbsp;Comm1&nbsp;Comm2&nbsp;MonthsPremium Monthly&nbsp;Comm1 Monthly&nbsp;Comm2 Monthly&nbsp;01/09/202031/08/202150,081.06-&nbsp;&nbsp;10,016.21&nbsp; 12.004,173.42-&nbsp;&nbsp;834.6801/05/202130/04/2022165,021.5323,103.0133,004.31&nbsp; 12.0013,751.791,925.252,750.3601/07/202130/06/202255,680.916,960.1111,136.18&nbsp; 12.004,640.08580.01928.0201/07/202130/06/202212,373.19556.792,474.64&nbsp; 12.001,031.1046.40206.2201/07/202130/06/2022171,295.917,708.3234,259.18&nbsp; 12.0014,274.66642.362,854.9301/07/202130/06/2022161,189.687,253.5432,237.94&nbsp; 12.0013,432.47604.462,686.4922/09/202131/08/202241,754.615,219.338,350.92&nbsp; 11.003,795.87474.48759.1722/09/202131/08/2022237,962.0329,745.2547,592.41&nbsp; 11.0021,632.912,704.114,326.5822/09/202131/08/2022785.4198.18157.08&nbsp; 11.0071.408.9314.2801/11/202131/10/202257,557.507,194.69-&nbsp;&nbsp;&nbsp; 12.004,796.46599.56-&nbsp;&nbsp;01/11/202131/10/202233,012.024,621.686,602.40&nbsp; 12.002,751.00385.14550.20&nbsp;

amitchandak · Accepted Answer

Anonymous , 
For M- Refer if this can help
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
&nbsp;
For DAX
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
&nbsp;
&nbsp;

Forum Discussion

Expand a table using start and end dates

2 Replies

Related content

expanded table

Expanded table

Expanded table

RTI Event Metadata Table Use Cases

Dynamically Expand Table Records

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