Missing dates - Dates Serie

[ricardocamargos]

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

[Phil_Seamark · Accepted solution]

HI @ricardocamargos

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 T2 = ADDCOLUMNS(T1 ,"Index",RANKX(T1,[Date],,ASC))
VAR T3 = NATURALLEFTOUTERJOIN(
                                ADDCOLUMNS(T2,"I",[Index] ),
                                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])

[MitchConnor]

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?

Many thanks in advance!

/MitchConnor

[Vinothsusai]

Hello Mitch,

I have a same scenario add the missing dates but the value should be 0. If you find the solution, could you please help me to resolve this.

Thanks

Vinoth

[ricardocamargos]

Thank you @Phil_Seamark