Filling a table that has sparse dates with newly generated dates between the existing ones

[pbiOP1]

Hello there!

I am trying to periodify the income represented by funds awarded to an organization, to create a continuous variable, representing the amount made available (regardless of amount spent) at any given point in time. The goal of this is to use the "Forecast" function of PBI´s default line chart, and have it forecast future trends based on past funding data.

My data currently has a Signature Date column with sparse values, so there is no continuity between dates. I have created a dummy table showing this:

ContractCode	SignatureDate	AmountGrant
Code1	01/01/2010	10
Code2	03/10/2010	20
Code3	10/12/2010	10
Code4	03/05/2013	30
Code5	04/06/2014	20
Code6	03/11/2014	70
Code7	05/02/2015	10
Code8	28/02/2015	20
Code9	29/10/2016	10
Code10	29/10/2016	30
Code11	21/09/2017	20
Code12	01/06/2018	70
Code13	01/01/2019	10
Code14	03/10/2019	20
Code15	10/12/2019	10

Because of this, PBI returns an error message when I try to use the prediction function: "Data are too irregular to create a forecast. Timescale is not uniform. Dates or numbers on a timescale must have coherent spaces between consecutive datapoints".

I take then I should create a table like the following one:

ContractCode	SignatureDate	AmountEUR	CumulativeAmount
Code1	01/01/2010	10	10
null	02/01/2010	null	10
null	03/01/2010	null	10
null	04/01/2010	null	10
null	05/01/2010	null	10
null	06/01/2010	null	10
null	07/01/2010	null	10
null	08/01/2010	null	10
null	09/01/2010	null	10
null	10/01/2010	null	10
null	11/01/2010	null	10
null	12/01/2010	null	10
null	13/01/2010	null	10
…	…	…	…
Code2	03/10/2010	20	30
null	04/10/2010	null	30
…	…	…	…
Code15	10/12/2019	10	360

I know how to create a Date Table (I already have one), but I have no clue on how to create a table such as the one I have dummied above, which fills the gap between two existing dates with the missing dates, leaving null values where there is no changes in the other variables but time does pass by.

My actual data consists on 4731 rows, with dates going back as far as 2009, so I don´t think doing this manually is an option.

Any help would be much appreciated (including any alternative solution to my problem that does not use the one I describe here).

Thanks in advance! 🙂

[tex628]

Create a new seperate calendar table with dates ranging from 2009 and forward. Create a relationship between the dates in you table and the dates i the calendar and finally use the calendar dates in the visual instead.

Hope it works,
Johannes

[jtownsend21 · Accepted solution]

@pbiOP1 I am not 100% or even 50% this will work, but I would try creating the following measure and using it as the value in your visual. 

SUM AmountGrant Test = 
CALCULATE(
    SUM(AmountGrant) + 0
) 

An alternate idea would be to create a column in your date table like the following: 

DATE Table Column Test = 
IF(
    SUM(AmountGrant) > 0, 
    SUM(AmountGrant), 
    0
)

I believe either of these should fill in the gaps between your dates with zeroes. However this still may not solve the issue as the values may still seem too eradic to the PBI forecast model.