cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ricardocamargos Established Member
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
Super User

Re: Missing dates - Dates Serie

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

2 REPLIES 2
Super User
Super User

Re: Missing dates - Dates Serie

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

ricardocamargos Established Member
Established Member

Re: Missing dates - Dates Serie

Thank you @Phil_Seamark