cancel
Showing results for
Did you mean:
Established Member

## Missing dates - Dates Serie

Hi there,

I have a dataset with missing dates. I've created a date table to supply those missing. However, I need to replicate the last nonblank value to the missing dates.

Example:

Dataset:

DATE - Product - Value

20171101 - Product A - 1

20171103 - Product A - 2

20171106 - Product A - 4

I need this visual:

DATE - Product - Value

20171101 - Product A - 1

20171102 - Product A - 1

20171103 - Product A - 2

20171104 - Product A - 2

20171105 - Product A - 2

20171106 - Product A - 4

Thank you,

Ricardo

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Missing dates - Dates Serie

You could try this calculated table.  It seems to work for me.  Just replace where I have the text Table2 with your own table name

```My Calc Table =

VAR T1 = 'Table2'
VAR T3 = NATURALLEFTOUTERJOIN(
SELECTCOLUMNS(T2,"D1",[Date],"I",[Index]-1)
)
VAR T4 = SELECTCOLUMNS(CALENDAR(MIN(Table2[Date]),MAX(Table2[Date])),"PivotDate",[Date])
VAR T5 =  FILTER(
CROSSJOIN(T3,T4),[PivotDate] >= [Date] && [PivotDate] < IF([D1]=BLANK(),DATE(2099,1,1) ,[D1]))
RETURN SELECTCOLUMNS(T5,"Date",[PivotDate],"Product" , [Product] , "Value" , [Value])```

Proud to be a Datanaut!

3 REPLIES 3
Super User

## Re: Missing dates - Dates Serie

You could try this calculated table.  It seems to work for me.  Just replace where I have the text Table2 with your own table name

```My Calc Table =

VAR T1 = 'Table2'
VAR T3 = NATURALLEFTOUTERJOIN(
SELECTCOLUMNS(T2,"D1",[Date],"I",[Index]-1)
)
VAR T4 = SELECTCOLUMNS(CALENDAR(MIN(Table2[Date]),MAX(Table2[Date])),"PivotDate",[Date])
VAR T5 =  FILTER(
CROSSJOIN(T3,T4),[PivotDate] >= [Date] && [PivotDate] < IF([D1]=BLANK(),DATE(2099,1,1) ,[D1]))
RETURN SELECTCOLUMNS(T5,"Date",[PivotDate],"Product" , [Product] , "Value" , [Value])```

Proud to be a Datanaut!

Established Member

## Re: Missing dates - Dates Serie

Thank you @Phil_Seamark

Frequent Visitor

## Re: Missing dates - Dates Serie

Hi Phil,

Many thanks for above solution. I have a similar problem and the solution does not work for me.

My data set:

Article:  Date:              Value:

12345   2018-01-01    1

12345   2018-01-02    5

12345   2018-01-05    3

54321   2018-01-01    10

54321   2018-01-10    20

I have about 30 000 articles and missing dates when the Value is 0. I would like to add all dates but with Value 0.

If i use the script you added i end up with all values on each Article for each day.

Do you mind helping me with this?