cancel
Showing results for
Did you mean:
Highlighted
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!

2 REPLIES 2
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])