Forum Discussion

RobX's avatar
RobX
Advocate I
5 years ago
Solved

Fill in Missing Dates

I have a table of weekly values that I want to break out into daily values.

 

Data is similar to below but is in Excel.

 

 

CREATE TABLE #tempTable (
Brand VARCHAR(100),
Date DATE,
Spend int)

INSERT INTO #tempTable
(
    Brand,
    Date,
    Spend
)
VALUES 
('A', '1/7/2021', 100),
('A',  '1/14/2021', 150),
('A', '1/21/2021', 200),
('B', '1/7/2021', 250),
('B', '1/14/2021', 200),
('B', '1/21/2021', 900),
('C', '1/7/2021', 200),
('C', '1/14/2021', 100),
('C', '1/21/2021', 50)

SELECT
	*
FROM #tempTable

 

 

 

I want to generate records for every day in between the available dates for each Brand so that the end result looks like this:

 

I found this suggestion on in an old StackOverflow post, but it only works to create a table with just Date and Spend.

FullTable = 
ADDCOLUMNS(
    CALENDAR(MIN(#tempTable[Date]), MAX(#tempTable[Date])),
    "Quantity",
    LOOKUPVALUE(
        #tempTable[Spend],
        #tempTable[Date],
        MAXX(
            FILTER(#tempTable, #tempTable[Date] <= EARLIER([Date])),
            [Date]
        )
    )
)

 

Does anyone have any idea how to expand it to include an additional dimension?

  •  

    let
        Source = #table({"Brand","Date","Spend"},{
            {"A", "1/7/2021", 100},
            {"A",  "1/14/2021", 150},
            {"A", "1/21/2021", 200},
            {"B", "1/7/2021", 250},
            {"B", "1/14/2021", 200},
            {"B", "1/21/2021", 900},
            {"C", "1/7/2021", 200},
            {"C", "1/14/2021", 100},
            {"C", "1/21/2021", 50}}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
        #"Transformed Column" = Table.TransformColumns(#"Changed Type", {"Date", each List.Reverse(List.Dates(_,7,-#duration(1,0,0,0)))}),
        #"Expanded Date" = Table.ExpandListColumn(#"Transformed Column", "Date")
    in
        #"Expanded Date"

     

     

     

    Expansion_DAX = 
    GENERATE (
        Base,
        ADDCOLUMNS ( GENERATESERIES ( 0, 6 ), "All Date", Base[Date] - [Value] )
    )

     

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion

     

    let
        Source = #table({"Brand","Date","Spend"},{
            {"A", "1/7/2021", 100},
            {"A",  "1/14/2021", 150},
            {"A", "1/21/2021", 200},
            {"B", "1/7/2021", 250},
            {"B", "1/14/2021", 200},
            {"B", "1/21/2021", 900},
            {"C", "1/7/2021", 200},
            {"C", "1/14/2021", 100},
            {"C", "1/21/2021", 50}}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
        #"Transformed Column" = Table.TransformColumns(#"Changed Type", {"Date", each List.Reverse(List.Dates(_,7,-#duration(1,0,0,0)))}),
        #"Expanded Date" = Table.ExpandListColumn(#"Transformed Column", "Date")
    in
        #"Expanded Date"

     

     

     

    Expansion_DAX = 
    GENERATE (
        Base,
        ADDCOLUMNS ( GENERATESERIES ( 0, 6 ), "All Date", Base[Date] - [Value] )
    )

     

    • RobX's avatar
      RobX
      Advocate I

      The DAX example was exactly what I needed.  Though one of the dates did get duplicated, but I'll find a way around that.

       

      Thanks!