Transforming Sporadic Forecast Data

[Anonymous]

I need help transforming some sporadic data I have in a table. I need it in a new table with all the products but with new rows for every date. The latest forecast is used for the new rows. Any help would be appreciated. Thanks.

Example:

Start					End
Product	forecast	date			Product	forecast	date
rice	10	1/1/2020			rice	10	1/1/2020
milk	5	1/3/2020			rice	10	1/2/2020
eggs	2	1/5/2020			rice	10	1/3/2020
rice	11	1/7/2020			rice	10	1/4/2020
eggs	5	1/7/2020			rice	10	1/5/2020
rice	9	1/9/2020			rice	10	1/6/2020
milk	3	1/9/2020			rice	11	1/7/2020
					rice	11	1/8/2020
					rice	9	1/9/2020
					rice	9	1/10/2020
					milk	5	1/3/2020
					milk	5	1/4/2020
					milk	5	1/5/2020
					milk	5	1/6/2020
					milk	5	1/7/2020
					milk	5	1/8/2020
					milk	3	1/9/2020
					milk	3	1/10/2020
					eggs	2	1/5/2020
					eggs	2	1/6/2020
					eggs	5	1/7/2020
					eggs	5	1/8/2020
					eggs	5	1/9/2020
					eggs	5	1/10/2020

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may refer to the posts below.

• https://community.powerbi.com/t5/Desktop/Get-value-of-next-incidence-of-pallet-with-specific-operation/m-p/748966#M360997
• https://community.powerbi.com/t5/Developer/Dax-Help-Create-table-with-variable-of-lines-according-to...

[kentyler]

It seems a piece is missing

Can there be a forecast for the same item on different dates ?

How is the date range of the "End" table determined ?

It looks like the partial rule is

Starting on the date of the forecast, create a record for every day for that product up to the end of the date range.
Is that a correct interpretation ?

[Anonymous]

Yes, forecasts for the same item come in on different dates. Essentaially the forecast gets updated with the most recent forecast recieved. This is then used until a new one comes in.

Your'e corret about the date range. The start is the first forecast recieved, and the end is the current date.